Example: Correlated subquery in a DELETE statement

 

When you use a correlated subquery in a DELETE statement, the correlation name represents the row that you want to delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row.

Suppose that a row in the CORPDATA.PROJECT table is deleted. Rows related to the deleted project in the CORPDATA.EMPPROJACT table must also be deleted. To do this, run the following statement:

  DELETE FROM CORPDATA.EMPPROJACT X     WHERE NOT EXISTS
       (SELECT *
          FROM CORPDATA.PROJECT           WHERE PROJNO = X.PROJNO)

SQL determines, for each row in the CORPDATA.EMP_ACT table, whether a row with the same project number exists in the CORPDATA.PROJECT table. If not, the CORPDATA.EMP_ACT row is deleted.

 

Parent topic:

Correlated subqueries