How to build subqueries into INSERT or UPDATE statements
Subqueries are used in two ways:
•In SELECT, UPDATE and DELETE statements, you can use subqueries in WHERE clauses to determine the subset of records to which the statement is to be applied. To define these WHERE subqueries, see "How to Specify a WHERE Condition".
•In INSERT and UPDATE statements, you can use subqueries to compute the values to be inserted or updated. These statements take the following forms respectively:
INSERT ... SELECT ...
UPDATE ... SET ... = subquery [WHERE ...]
To build an INSERT ... SELECT statement:
If you use a subquery for values, there can be only one subquery, which should provide all the values required for the INSERT.
•Constants or expressions can not be used and are therefore ignored. They should become a part of the subquery.
•Because most subqueries refer to the table in the main statement (correlated subquery), the SQL Definer places this table in the From List by default; this is not necessarily helpful in the case of INSERT. If it is not, begin by removing this correlated table.
To build an UPDATE .. SET ... subquery [WHERE ...] statement:
SUBQUERY( name )
NOTE: Multiple subqueries are possible. Constants and expressions can also be used for certain columns.