13.8. Using Subqueries in Updates

Use of subqueries is not limited to SELECT statements. Any SQL statement that includes a WHERE clause or scalar expression may use subqueries. For example, to create a new table containing every North American city, and then later remove all cities located in countries where the life expectancy is less than 70 years, use these statements:

 mysql> CREATE TABLE NACities     -> SELECT * FROM City     -> WHERE CountryCode IN (SELECT Code     ->                       FROM Country     ->                       WHERE Continent='North America'); Query OK, 581 rows affected (0.07 sec) Records: 581  Duplicates: 0  Warnings: 0 mysql> DELETE FROM NACities     -> WHERE CountryCode IN (SELECT Code     ->                       FROM Country     ->                       WHERE LifeExpectancy < 70.0); Query OK, 26 rows affected (0.00 sec) 

Although subqueries can be used to retrieve or aggregate data from other tables for use in statements that modify tables (such as UPDATE, DELETE, INSERT, and REPLACE), MySQL does not allow a table that is being updated in the outer query to be selected from in any subquery of the statement. For example, the following statement yields an error:

 mysql> DELETE FROM NACities     -> WHERE ID IN (SELECT ID     ->              FROM NACities     ->              WHERE Population < 500); ERROR 1093 (HY000): You can't specify target table 'NACities' for update in FROM clause