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.
SQL Statement | NEW TABLE | OLD TABLE |
|---|---|---|
INSERT | Yes | No |
UPDATE | Yes | Yes |
DELETE | No | Yes |
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