Selecting values while deleting data

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

About this task

You can select values from rows that are being deleted by specifying 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

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.

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');

To retrieve row-by-row output of deleted data, use a cursor with a SELECT FROM DELETE statement.

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;

If you need to retrieve calculated data based on the data that you delete but not add that column to the target table.

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');

Parent topic: Deleting data from tables