Here is a very basic example. You have two tables TableA, TableB and you want to update a field, 'late', in TableA whenever TableA.student_number = TableB.student_number.
UPDATE TableA as a
SET `late` = 't' WHERE a.student_id IN
( SELECT student_id
FROM TableB
)
Subqueries are very powerful (and dangerous if you don't know what you're doing) and elegant. Setup: I have two tables request and details. The tables contain 13 and 10 fields respectively, but for our purposes, here is the relevant schema:
mysql> desc request;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| details_id | int(11) | NO | | 0 | |
| date | int(11) | NO | | 0 | |
mysql> desc details;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
+----------------------+--------------+------+-----+---------+----------------+
| details_id | int(11) | NO | UNI | 0 |
| actual_completion | int(11) | YES | | NULL |
Objective: In one query (with subqueries), I want to update the request table's date field to be equal to the actual_completion field, but only if the actual_completion is before May 1st, 2007, or roughly a Unix Epoch Timestamp of 1177977600. Solution: attempt #1
UPDATE request as r
SET `date` =
( SELECT d.actual_completion
FROM details d
WHERE d.details_id = r.details_id AND
d.actual_completion < 1177977600
)
This almost works. However, every record in request is updated. Where the inner query has no results, the outer record r just sets `date` to 0. This is not the desired result. attempt #2
UPDATE request as r
SET `date` =
( SELECT d.actual_completion
FROM details d
WHERE d.details_id = r.details_id
)
WHERE details_id IN
(SELECT d.details_id as details_id FROM details d where
d.actual_completion < 1177977600)
I have removed the WHERE restriction outside of the inner query, and added the it to the outer query, checking that the details_id is in the result set of details with `actual_completion` below 1177977600. So now, only request records with associated details that match the criteria get updated. This produces the desired results. Hooray! A little later, I decided that I wanted to also adjust all the records entered in by a certain user. The request table has a field `entered_by` that keeps track of this. We can combine the subquery with the table request table alias to accomplish this.
UPDATE request as r
SET `date` =
( SELECT d.actual_completion
FROM details d
WHERE d.details_id = r.details_id
)
WHERE r.entered_by = 'username';
Here are two much more extensive links:
dev.mysql.com subquery
http://www.weberdev.com/ViewArticle-429.html
All Articles