Subqueries in the WHERE Clause of DELETE

The FROM clause of a subquery in the WHERE clause of the DELETE statement can specify as a data source the same table or view that the FROM clause of the DELETE statement specifies. DELETE 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 DELETE statement WHERE clause, using Condition with Subquery syntax.
  • Any SPL routines within the subquery cannot reference the table that is being modified.

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

The following example deletes from the orders table a subset of rows whose paid_date column value satisfies the condition in the WHERE clause. The WHERE clause specifies which rows to delete by applying the IN operator to the rows returned by a subquery that selects only the rows of the orders table where the paid_date value is earlier than the current date:
DELETE FROM orders WHERE paid_date IN
   (SELECT paid_date FROM orders WHERE paid_date < CURRENT );

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 orders table that the FROM clause of the outer UPDATE statement specifies. The previous example illustrates HCL OneDB™ support for uncorrelated subqueries in the WHERE clause of the DELETE statement. rather than how to write short SQL statements. The next example achieves the same result with simpler syntax:

DELETE orders WHERE paid_date < CURRENT;

The following example deletes from the stock table the row (or rows) with the largest unit_price value. The WHERE clause identifies which unit_price value is the largest by applying the equality operator to the result of a subquery that calls the built-in MAX aggregate function for the unit_price column values:

DELETE FROM stock WHERE unit_price = 
   (SELECT MAX(unit_price) FROM stock );

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

A subquery in the DELETE statement can include the UNION or UNION ALL operators.

If the table that the outer DELETE 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 DELETE:
  • DELETE from parent table with subquery ( SELECT from parent table )
  • DELETE from parent table with subquery ( SELECT from child table )
  • DELETE from child table with subquery ( SELECT from parent table )
  • DELETE from child table with subquery ( SELECT from child table ).

See the Condition with Subquery topic for more information about the syntax of subqueries to return multiple rows as predicates in the WHERE clause of the DELETE statement.