subqueries make life easy
Sep 13, 2009 · 2 minute read · Commentscode
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 :)