Learning Visually with Examples [Electronic resources] نسخه متنی

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]
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