Subqueries in DELETE and UPDATE statements

Certain restrictions apply. If the FROM clause of a subquery returns more than one row, and the clause specifies the same table or view that the outer DML statement is modifying, the DML operation will succeed under these circumstances:

• The DML statement is not an INSERT statement.
• No SPL routine within the subquery references the table that is being modified.
• The subquery does not include a correlated column name.
• The subquery is specified using the Condition with Subquery syntax in the WHERE clause of the DELETE or UPDATE statement.

If any of these conditions are not met, the DML operation fails with error -360.

The following example updates the stock table by increasing the unit_price value by 10% for a subset of prices. The WHERE clause specifies which prices to increase by applying the IN operator to the rows returned by a subquery that selects only the rows of the stock table where the unit_price value is less than 75.

UPDATE stock SET unit_price = unit_price * 1.1 
   WHERE unit_price IN
      (SELECT unit_price FROM stock WHERE unit_price < 75);