Using a Subquery to Update Multiple Column Values
The expression list can include one or more subqueries. Each must return a single row containing one or more values. The number of columns that the SET clause explicitly or implicitly specifies must equal the number of values returned by the expression (or expression list) that follows the equal ( = ) sign in the multiple-column SET clause.
The subquery must be enclosed between parentheses. These
parentheses are nested within the parentheses that immediately follow
the equal ( = ) sign. If the expression list includes multiple subqueries,
each subquery must be enclosed between parentheses, with a comma (
, ) separating successive subqueries:
UPDATE ... SET ... = ((subqueryA),(subqueryB), ... (subqueryN))
The following examples show the use of subqueries in the
SET clause:
UPDATE items SET (stock_num, manu_code, quantity) = ( (SELECT stock_num, manu_code FROM stock WHERE description = 'baseball'), 2) WHERE item_num = 1 AND order_num = 1001; UPDATE table1 SET (col1, col2, col3) = ((SELECT MIN (ship_charge), MAX (ship_charge) FROM orders), '07/01/2007') WHERE col4 = 1001;
If you are updating a supertable in a table hierarchy, the SET clause cannot include a subquery that references one of its subtables. If you are updating a subtable in a table hierarchy, a subquery in the SET clause can reference the supertable if it references only the supertable. That is, the subquery must use the SELECT...; FROM ONLY (supertable) syntax.