Selecting values while updating data

When you update rows in a table, you can select the updated values from those rows at the same time.

Procedure

To select values from rows that are being updated:

Specify the UPDATE statement in the FROM clause of the SELECT statement.

When you update one or more rows in a table, you can retrieve:

The value of an automatically generated column such as a ROWID or identity column
Any default values for columns
All values for an updated row, without specifying individual column names

In most cases, you want to use the FINAL TABLE clause with SELECT FROM UPDATE statements. The FINAL TABLE consists of the rows of the table or view after the update occurs.

Examples

Example: SELECT FROM FINAL TABLE

Suppose that all clerks for a company are receiving 5 percent raises. You can use the following SELECT FROM UPDATE statement to increase the salary of each designer by 5 percent and to retrieve the total increase in salary for the company.

SELECT SUM(SALARY) INTO :salary FROM FINAL TABLE
  (UPDATE EMP SET SALARY = SALARY * 1.05
   WHERE JOB = 'DESIGNER');

Example: retrieving data row-by-row from updated data

To retrieve row-by-row output of updated data, use a cursor with a SELECT FROM UPDATE statement. For example, suppose that all designers for a company are receiving a 30 percent increase in their bonus. You can use the following SELECT FROM UPDATE statement to increase the bonus of each clerk by 30 percent and to retrieve the bonus for each clerk.

DECLARE CS1 CURSOR FOR
  SELECT LASTNAME, BONUS FROM FINAL TABLE
   (UPDATE EMP SET BONUS = BONUS * 1.3
    WHERE JOB = 'CLERK');
FETCH CS1 INTO :lastname, :bonus;

Example: INCLUDE a new column in the result table but not the target table

You can use the INCLUDE clause to introduce a new column to the result table but not add the column to the target table. For example, suppose that sales representatives received a 20 percent increase in their commission. You need to update the commission (COMM) of sales representatives (SALESREP) in the EMP table and that you need to retrieve the old commission and the new commission for each sales representative. You can use the following SELECT FROM UPDATE statement to perform the update and to retrieve the required data.

DECLARE CS2 CURSOR FOR
SELECT LASTNAME, COMM, old_comm FROM FINAL TABLE
  (UPDATE EMP INCLUDE(old_comm DECIMAL (7,2))
   SET COMM = COMM * 1.2, old_comm = COMM
   WHERE JOB = 'SALESREP');