Using a Subquery to Update a Column

You can update a column with the value that a subquery returns.

UPDATE orders
   SET ship_charge =
      (SELECT SUM(total_price) * .07 FROM items
         WHERE orders.order_num = items.order_num)
      WHERE orders.order_num = 1001

In Dynamic Server, if you are updating a supertable in a table hierarchy, the SET clause cannot include a subquery that references a subtable. 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.