Understanding DB2: Learning Visually with Examples (2nd Edition)

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.

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]