Example: Correlated subquery in an UPDATE statement

 

When you use a correlated subquery in an UPDATE statement, the correlation name refers to the rows that you want to update.

For example, when all activities of a project must be completed before September 1983, your department considers that project to be a priority project. You can use the following SQL statement to evaluate the projects in the CORPDATA.PROJECT table, and write a 1 (a flag to indicate PRIORITY) in the PRIORITY column (a column you added to CORPDATA.PROJECT for this purpose) for each priority project.

  UPDATE CORPDATA.PROJECT X     SET PRIORITY = 1
    WHERE '1983-09-01' >
       (SELECT MAX(EMENDATE)
           FROM CORPDATA.EMPPROJACT            WHERE PROJNO = X.PROJNO)

As SQL examines each row in the CORPDATA.EMPPROJACT table, it determines the maximum activity end date (EMENDATE) for all activities of the project (from the CORPDATA.PROJECT table). If the end date of each activity associated with the project is before September 1983, the current row in the CORPDATA.PROJECT table qualifies and is updated.

Update the master order table with any changes to the quantity ordered. If the quantity in the orders table is not set (the NULL value), keep the value that is in the master order table.

UPDATE MASTER_ORDERS X    SET QTY=(SELECT COALESCE (Y.QTY, X.QTY)
              FROM ORDERS Y               WHERE X.ORDER_NUM = Y.ORDER_NUM)
   WHERE X.ORDER_NUM IN (SELECT ORDER_NUM 
              FROM ORDERS)

In this example, each row of the MASTER_ORDERS table is checked to see if it has a corresponding row in the ORDERS table. If it does have a matching row in the ORDERS table, the COALESCE function is used to return a value for the QTY column. If QTY in the ORDERS table has a non-null value, that value is used to update the QTY column in the MASTER_ORDERS table. If the QTY value in the ORDERS table is NULL, the MASTER_ORDERS QTY column is updated with its own value.

 

Parent topic:

Correlated subqueries