Section 9.3. Selecting from UPDATE, DELETE, and INSERT

9.3. Selecting from UPDATE, DELETE, and INSERT

While the INSERT, UPDATE, and DELETE statements change data in the specified tables, they only return a message indicating whether the statement completed successfully. If the statement completed successfully, you need to issue a separate SQL statement to find out what changed. In the next example, to determine which rows are to be deleted, you first issue a SELECT statement to capture the rows you will delete on the following DELETE statement. Both statements have the same WHERE condition to filter the same rows.

 SELECT empno, firstnme, lastname FROM employee WHERE workdept = 'A00'; DELETE FROM employee WHERE workdept = 'A00'; 

Rather than issuing two separate statements, they can be optimized to use just one SQL statement like this:

 SELECT empno, firstnme, lastname FROM OLD TABLE (DELETE FROM employee WHERE workdept = 'A00'); 

Whenever a table is inserted, updated, or deleted, DB2 maintains one or more internal temporal tables known as transition tables. You specify the transition tables with the NEW TABLE and OLDTABLE clauses. Depending on the SQL operation, different transition tables are available. Refer to Table 9.2 for a summary of their availability.

Table 9.2. Availability of Transition Tables Depending on the SQL Statement Issued

SQL Statement	NEW TABLE	OLD TABLE
INSERT	Yes	No
UPDATE	Yes	Yes
DELETE	No	Yes

To demonstrate a SELECT from UPDATE, consider the following example in which you want to increase the salary of all the employees in department A00. Using OLD TABLE lets you perform the update as well as return the new salaries.

 SELECT salary    FROM OLD TABLE (UPDATE employee                       SET salary = salary * 1.1                     WHERE workdept = 'A00') 

Similarly, if you want to retrieve the new salary, you can use NEW TABLE instead:

 SELECT salary   FROM NEW TABLE (UPDATE employee                      SET salary = salary * 1.1                    WHERE workdept = 'A00') 

SELECT from INSERT works just like the preceding example:

 SELECT salary    FROM NEW TABLE (INSERT INTO employee                     (empno, firstnme, midinit, lastname, edlevel)                    VALUES ( '000999', 'SMITH', 'A', 'JOHN', 18 )) 

You cannot retrieve both the new and old salary values by using NEW TABLE and OLD TABLE alone. To do this, use the INCLUDE clause.

 SELECT salary as new_salary, old_salary   FROM NEW TABLE ( UPDATE employee INCLUDE (old_salary DECIMAL(9,2))                       SET salary     = salary * 1.10,                           old_salary = salary                     WHERE workdept = 'A00') 

The INCLUDE clause in the nested UPDATE statement creates a new column that can be selected from the outer SELECT statement. You can see that the old_salary gets the old salary value while the table column salary is increased by 10%.

Finally, you should also know about the FINAL TABLE clause. When executing an INSERT, UPDATE, or DELETE statement, there may still be AFTER TRiggers or referential constraints that result in further modification of data in the target table. Using FINAL TABLE can prevent these types of changes.

For instance, assume that an AFTER TRigger is defined to delete all rows from the employee table when an employee's salary is updated. If FINAL TABLE is used, the UPDATE statement will fail. This protects you from any unforeseen side-effects not visible to the application.

For example, an error is received if the following SQL statement is issued:

[View full width]
 SELECT salary   FROM FINAL TABLE (UPDATE employee                        SET salary = salary * 1.1                      WHERE workdept = 'A00') SQL0989N  AFTER trigger "AUDIT_TRIG" attempted to modify a row in table "EMPLOYEE" that was modified by an SQL data change statement within a FROM clause.  SQLSTATE=560C3