Selecting values while deleting data

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

Procedure

You can select values from rows that are being deleted:

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

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

Any default values for columns
All values for a deleted row, without specifying individual column names
Calculated values based on deleted rows

Example

Example: FROM OLD TABLE clause

When you use a SELECT FROM DELETE statement, you must use the FROM OLD TABLE clause to retrieve deleted values. The OLD TABLE consists of the rows of the table or view before the delete occurs. For example, suppose that a company is eliminating all operator positions and that the company wants to know how much salary money it will save by eliminating these positions. You can use the following SELECT FROM DELETE statement to delete operators from the EMP table and to retrieve the sum of operator salaries.

SELECT SUM(SALARY) INTO :salary FROM OLD TABLE
  (DELETE FROM EMP
   WHERE JOB = 'OPERATOR');

Example: retrieving row-by-row output of deleted data

To retrieve row-by-row output of deleted data, use a cursor with a SELECT FROM DELETE statement. For example, suppose that a company is eliminating all analyst positions and that the company wants to know how many years of experience each analyst had with the company. You can use the following SELECT FROM DELETE statement to delete analysts from the EMP table and to retrieve the experience of each analyst.

DECLARE CS1 CURSOR FOR 
SELECT YEAR(CURRENT DATE - HIREDATE) FROM OLD TABLE
  (DELETE FROM EMP
   WHERE JOB = 'ANALYST');
FETCH CS1 INTO :years_of_service;

Example: retrieving calculated data based on deleted dable

If you need to retrieve calculated data, based on the data that you delete but not add that column to the target table. For example, suppose that you need to delete managers from the EMP table and that you need to retrieve the salary and the years of employment for each manager. You can use the following SELECT FROM DELETE statement to perform the delete operation and to retrieve the required data.

DECLARE CS2 CURSOR FOR
SELECT LASTNAME, SALARY, years_employed FROM OLD TABLE
  (DELETE FROM EMP INCLUDE(years_employed INTEGER)
   SET years_employed = YEAR(CURRENT DATE - HIREDATE)
   WHERE JOB = 'MANAGER');