Selecting updated values

You can retrieve the values for rows that are being updated by specifying the UPDATE statement in the FROM clause of a SELECT statement.

When you update one or more rows of a table, you can select the modified rows from the update operation. These rows can contain the values before the update is performed or after the update is performed.

To update the salary for an employee and return the prior salary value, use the following statement:

SELECT EMPNO, SALARY
  FROM OLD TABLE (UPDATE EMPLOYEE 
                         SET SALARY = SALARY * 1.05 
                         WHERE EMPNO = '000280');

You can return additional information from the update statement by using the INCLUDE clause. To update the salary for all employees in department E21 and return the new and old salary values, use the following statement.

SELECT EMPNO, WORKDEPT, OLD_SALARY, SALARY AS NEW_SALARY
  FROM FINAL TABLE (UPDATE EMPLOYEE 
                         INCLUDE (OLD_SALARY DECIMAL(9,2)) 
                         SET SALARY = SALARY * 1.05, OLD_SALARY = SALARY
                         WHERE WORKDEPT = 'E21');