subqueries make life easy

Sep 13, 2009 · 2 minute read
code
mysql

often times while working with databases, you find the need to do some simple tasks (adding a column and populating it with a value from another table, etc). rather than write a script to do this, use mysql’s native subquery (and temporary table) functionality - it makes life much easier.

consider the case where you have two tables:

mysql> describe colors;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       | 
| color | varchar(10) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and

mysql> describe color_mapping;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| picture_id | int(11) | YES  |     | NULL    |       | 
| color_id   | int(11) | YES  |     | NULL    |       | 
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

let’s say you want to update the colors table to add a frequency column such that you know how often a given color is used. it turns out this is really easy using mysql:

alter table colors add frequency int;
update colors set frequency = (select count(*) from color_mapping \
   where color_mapping.color_id = colors.id);

and that’s it. also useful are temporary tables:

create temporary table color_frequencies select color_id, count(*) as cnt \
   from color_mapping group by color_id;

if you then attempt to desc color_frequencies, you’ll see a table with two columns - a color_id and a cnt column.

hopefully this will save some people some efforts writing scripts next time some simple database updates are needed :)