Subqueries in the WHERE Clause of UPDATE

The FROM clause of a subquery in the WHERE clause of the UPDATE statement can specify as a data source the same table or view that the Table Options clause of the UPDATE statement specifies. UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true:
  • The subquery either returns a single row, or else has no correlated column references.
  • The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
  • No SPL routine in the subquery can reference the same table that UPDATE is modifying.

Unless all of these conditions are satisfied, UPDATE statements that include subqueries that reference the same table or view that the UPDATE statement modifies return error -360.

The following example updates the stock table by reducing the unit_price value by 5% for a subset of prices. The WHERE clause specifies which prices to reduce 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 greater than 50: 
UPDATE stock SET unit_price = unit_price * 0.95 
   WHERE unit_price IN
      (SELECT unit_price FROM stock WHERE unit_price > 50);

This subquery includes only uncorrelated column references, because its only referenced column is in a table specified in its FROM clause. The requirements listed above are in effect, because the data source of the subquery is the same stock table that the Table Options clause of the outer UPDATE statement specifies.

The previous example produces the same results as issuing two separate DML statements:
  • The SELECT statement, to return a temporary table, tmp1, that contains the same rows from the stock table that the subquery returned.
  • The UPDATE statement, to issue a subquery of the temporary table as a predicate in its WHERE clause to modify every row of the stock table where the unit_price matches a value in the temporary table: 
SELECT unit_price FROM stock WHERE unit_price > 50 INTO TEMP tmp1;
UPDATE stock SET unit_price = unit_price * 0.95 
   WHERE unit_price IN ( SELECT * FROM tmp1 );

Here is an example of a more complex UPDATE statement that includes multiple uncorrelated subqueries in its WHERE clause:

UPDATE t1 SET a = a + 10 
   WHERE a > ALL (SELECT a FROM t1  WHERE a > 1) AND
         a > ANY (SELECT a FROM t1  WHERE a > 10) AND
          EXISTS (SELECT a FROM t1  WHERE a > 5);;

If an enabled Select trigger is defined on a table that is the data source of a subquery in the WHERE clause of an UPDATE statement that modifies the same table, executing that subquery within the UPDATE statement does not activate the Select trigger. Consider the following program fragment:

CREATE TRIGGER selt11 SELECT ON t1 BEFORE
  (UPDATE d1 
     SET (c1, c2, c3, c4, c5) =
         (c1 + 1, c2 + 1, c3 + 1, c4 + 1, c5 + 1));

UPDATE t2 SET c1 = c1 +1 
   WHERE c1 IN 
      (SELECT t1.c1 from t1 WHERE t1.c1 > 10 );

In the example above, trigger selt11 is not activated as part of the UPDATE operation on table t2.

A subquery in the WHERE clause of the UPDATE statement can include the UNION or the UNION ALL operator, as in the following example.

UPDATE t1 SET a = a + 10 WHERE a in (SELECT a FROM t1 WHERE a > 1
   UNION SELECT a FROM t1, t2 WHERE a < b);

If the table that the outer UPDATE statement modifies a typed table within a table hierarchy, HCL OneDB™ supports all of the following operations that use valid subqueries in the WHERE clause of UPDATE:

  • UPDATE on target parent table with subquery ( SELECT from parent table )
  • UPDATE on target parent table with subquery ( SELECT from child table )
  • UPDATE on target child table with subquery ( SELECT from parent table )
  • UPDATE on target child table with subquery ( SELECT from child table ).

The following program fragment illustrates UPDATE operations with subqueries on typed tables:

CREATE ROW TYPE r1 (c1 INT, c2 INT);
CREATE ROW TYPE r2 UNDER r1;
CREATE TABLE t1 OF TYPE r1; -- parent table 
CREATE TABLE t2 OF TYPE r2 UNDER t1; -- child  table 

UPDATE t1 SET c1 = c1 + 1 WHERE c1 IN
   ( SELECT t1.c1 FROM t1 WHERE t1.c1 > 10);

UPDATE t1 SET c1 = c1 + 1 WHERE c1 IN 
   ( SELECT t2.c1 FROM t2 WHERE t2.c1 > 10);  

UPDATE t2 SET c1 = c1 + 1 WHERE c1 IN 
   ( SELECT t2.c1 FROM t2 WHERE t2.c1 > 10);

UPDATE t2 SET c1 = c1 + 1 WHERE c1 IN  
   ( SELECT t1.c1 FROM t1 WHERE t1.c1 > 10);

See the Condition with Subquery topic for more information about how to use subqueries that return multiple rows as predicates in the WHERE clause of the UPDATE statement.