SELECT FROM data-change statements in DB2

Data-change-table-reference

The intermediate result is the set of rows directly affected by the nested INSERT, searched UPDATE, searched DELETE, or MERGE. IBM syntax (simplified):

1
2
3
4
5
FINAL TABLE ( INSERT … )
FINAL TABLE ( searched UPDATE … )
OLD TABLE   ( searched UPDATE … )
OLD TABLE   ( searched DELETE … )
FINAL TABLE ( MERGE … )

Rules beginners must memorize:

• It must be the only table-reference in that FROM clause
• The fullselect must be the outer fullselect of a select-statement or SELECT INTO
• A cursor that uses it is read-only (no positioned UPDATE/DELETE on that cursor)
• You need SELECT privilege on the target as well as the insert/update /delete/merge privilege
• Result encoding scheme must match the target table or view
• The intermediate table has every target column (including implicitly hidden) plus any INCLUDE columns
• Nested INSERT/UPDATE/DELETE/MERGE must not correlate to columns outside that nested statement

Content is determined when the cursor is opened (or when SELECT INTO runs). Later searched updates in the same program do not change that already-built result. Scrollable cursors over SELECT FROM INSERT must be INSENSITIVE.

SELECT FROM INSERT

Nest INSERT inside FINAL TABLE. The result includes generated identity / ROWID / row-change-timestamp values, defaults, and BEFORE INSERT trigger assignments. Constraints are enforced before the result is produced. AFTER INSERT triggers do not change those result values; AFTER triggers that further modify the target are rejected with FINAL TABLE.

1
2
3
4
5
6
-- EMPNO is GENERATED ALWAYS AS IDENTITY
SELECT EMPNO, HIRETYPE, HIREDATE
  FROM FINAL TABLE (
        INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
        VALUES ('Mary Smith', 35000.00, 11, 'Associate')
       );

Host-language form:

1
2
3
4
5
6
SELECT EMPNO, HIRETYPE, HIREDATE
  INTO :HV-EMPNO, :HV-HIRETYPE, :HV-HIREDATE
  FROM FINAL TABLE (
        INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
        VALUES (:NAME, :SALARY, :DEPTNO, :LEVEL)
       );

Target of the INSERT must be a base table, a view with WITH CASCADED CHECK, or a view with no WHERE clause. If the view has a search condition, rows that would not satisfy the view are not inserted (the INSERT fails). Multiple-row INSERT inside SELECT cannot be NOT ATOMIC when other host variables appear in the outer fullselect, and cannot be atomic multi-row with USING DESCRIPTOR in that situation.

Multiple-row INSERT and INPUT SEQUENCE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE CS1 CURSOR FOR
SELECT EMP_ROWID
  FROM FINAL TABLE (
        INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
        SELECT EMPNO FROM DSN8C10.EMP
       );
 
-- Preserve insert order from a host-variable array
DECLARE CS3 CURSOR FOR
SELECT EMP_ROWID
  FROM FINAL TABLE (
        INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
        VALUES (:hva_empno)
        FOR 5 ROWS
       )
 ORDER BY INPUT SEQUENCE;

ORDER BY INPUT SEQUENCE is valid only when INSERT is in the SELECT’s FROM clause. FETCH FIRST n ROWS ONLY on the outer SELECT limits how many of those inserted rows you fetch; it does not stop the INSERT of the remaining rows—the INSERT still runs as written. To insert fewer rows, change the INSERT itself.

INCLUDE columns

INCLUDE (col type, …) on the nested INSERT adds result columns that are not stored in the target. The INSERT fullselect (or VALUES) supplies values for target columns and INCLUDE columns together.

1
2
3
4
5
6
7
DECLARE CS1 CURSOR FOR
SELECT manager_num, projname
  FROM FINAL TABLE (
        INSERT INTO PROJ (DEPTNO)
        INCLUDE (manager_num CHAR(6))
        SELECT DEPTNO, MGRNO FROM DEPT
       );

SELECT FROM UPDATE

Use a searched UPDATE (WHERE, not WHERE CURRENT OF). FINAL TABLE returns after-images; OLD TABLE returns before-images. The UPDATE cannot correlate outside itself. AFTER triggers that further change the target are not allowed. Views must be symmetric or WITH CASCADED CHECK OPTION (IBM: if the SELECT FROM UPDATE references a view, define it WITH CASCADED CHECK OPTION).

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT EMPNO, SALARY
  FROM FINAL TABLE (
        UPDATE DSN8C10.EMP
           SET SALARY = SALARY * 1.10
         WHERE JOB = 'CLERK'
       );
 
SELECT EMPNO, SALARY AS OLD_SALARY
  FROM OLD TABLE (
        UPDATE DSN8C10.EMP
           SET SALARY = SALARY * 1.10
         WHERE JOB = 'CLERK'
       );

A searched UPDATE inside SELECT does not clear AREO* status of a table (an IBM restriction to be aware of for REORG/advisory states).

SELECT FROM DELETE

Only OLD TABLE applies: you cannot select rows after they are gone. Searched DELETE only; no correlation to the outer query; same view and AFTER-trigger restrictions as UPDATE.

1
2
3
4
5
6
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM OLD TABLE (
        DELETE FROM DSN8C10.EMP
         WHERE WORKDEPT = 'E21'
           AND JOB = 'FIELDREP'
       );

Typical uses: write an audit table in the same unit of work from the returned old rows, or return to a client what was removed. The DELETE still happens when the SELECT runs (at OPEN for a cursor).

SELECT FROM MERGE

FINAL TABLE (MERGE …) returns the rows MERGE inserted or updated, as they exist at completion. Extra IBM rules include:

• Target must be a base table or an eligible view (WITH CASCADED CHECK or no WHERE)
• No ROWID column on the MERGE target; with NOT ATOMIC CONTINUE ON SQLEXCEPTION, no LOB or XML columns on the target either
• If MERGE uses a table-reference source, AFTER triggers that further change the target must not exist, and that source must not correlate outside the MERGE
• With VALUES source and NOT ATOMIC (or when NOT ATOMIC is omitted with VALUES), the MERGE must not include a delete operation

1
2
3
4
5
6
7
8
9
10
SELECT ACCT, AMT
  FROM FINAL TABLE (
        MERGE INTO ACCOUNT AS T
        USING (SELECT ACCT, AMT FROM STAGE) AS S
           ON T.ACCT = S.ACCT
         WHEN MATCHED THEN
           UPDATE SET T.AMT = T.AMT + S.AMT
         WHEN NOT MATCHED THEN
           INSERT (ACCT, AMT) VALUES (S.ACCT, S.AMT)
       );

Views, INSTEAD OF, and privileges

FINAL TABLE with a view that has an INSTEAD OF trigger for that change type is an error. OLD TABLE can be used when selecting expressions from a view in some cases where FINAL TABLE cannot (IBM restricts NEXT VALUE, non-deterministic / external action functions, and functions that read or modify SQL data in view expressions unless OLD TABLE is specified).

Explain It Like I'm Five

INSERT is putting a new toy in the box. Sometimes the box stamps a secret number on the toy. SELECT FROM INSERT is looking at the toy while you put it in, so you can read the stamp immediately. FINAL TABLE is “how it looks after the box’s rules.” OLD TABLE is a photo from before you changed or threw the toy away. You are not allowed to dump two boxes into the same glance—the special FROM clause may list only that one change.

Exercises

1. Write SELECT FROM INSERT that returns a GENERATED ALWAYS identity value into a host variable.
2. Explain why FINAL TABLE (DELETE …) is the wrong qualifier.
3. Contrast FINAL TABLE vs OLD TABLE for a 10% salary UPDATE.
4. Add INCLUDE(src CHAR(8)) to a SELECT FROM INSERT that copies from a staging table, and say whether SRC is stored in the target.
5. State whether you can FROM FINAL TABLE (INSERT …) JOIN DEPT — and how to get department names instead.