Using a Subquery to Update 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.

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/1997')
   WHERE col4 = 1001 

In Dynamic Server, 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.