The DELETE statement in DB2 SQL

DELETE

The searched form is:

1
2
DELETE FROM HR.EMP_STAGE
 WHERE LOAD_DATE < CURRENT DATE - 30 DAYS;

Each row for which the search condition is TRUE is deleted. FALSE and UNKNOWN are kept. You need DELETE privilege on the table. SQLERRD(3) is normally the number of rows deleted. A searched DELETE with no qualifying rows is still SQLCODE 0 with a count of zero—not “row not found” unless your shop maps it that way in a procedure.

Omit WHERE and you delete every row: a mass DELETE. Logging, locking, delete triggers, and RI still apply. For “empty this table fast and maybe skip triggers,” see TRUNCATE on the next related page. Mass DELETE is the right tool when you must fire triggers or when TRUNCATE is blocked (parent in enforced RI, system-period temporal table, and similar restrictions).

DELETE with subqueries

WHERE can contain IN, EXISTS, quantified predicates, or comparisons with a scalar subquery. Non-correlated subqueries run once. Correlated subqueries may run per candidate row.

1
2
3
4
5
6
7
8
9
10
-- Employees who are not on any project
DELETE FROM HR.EMPLOYEE E
 WHERE NOT EXISTS (
       SELECT 1
         FROM HR.EMPPROJACT P
        WHERE P.EMPNO = E.EMPNO);
 
-- Remove staging rows whose EMPNO is already in the master
DELETE FROM HR.EMP_STAGE S
 WHERE S.EMPNO IN (SELECT EMPNO FROM HR.EMPLOYEE);

If the subquery refers to the same table you are deleting from, or to a dependent table whose delete rule is CASCADE or SET NULL, Db2 evaluates the subquery completely before any row is deleted. That stops the statement from seeing a half-deleted set mid-flight. Write the subquery as if the table were still fully populated, then let Db2 apply the deletes.

DELETE using a cursor

Positioned DELETE is the companion of FETCH in embedded SQL. The table in DELETE FROM must be the table (or view) in the cursor’s FROM clause, and the cursor must not be read-only. FOR UPDATE on the SELECT is the usual way to keep the cursor updatable, especially under isolation UR.

1
2
3
4
5
6
7
8
9
10
11
12
13
       EXEC SQL
            DECLARE C1 CURSOR FOR
             SELECT EMPNO
               FROM HR.EMP_STAGE
              WHERE STATUS = 'REJECT'
                FOR UPDATE
       END-EXEC.
       EXEC SQL OPEN C1 END-EXEC.
       EXEC SQL FETCH C1 INTO :HV-EMPNO END-EXEC.
       EXEC SQL
            DELETE FROM HR.EMP_STAGE
             WHERE CURRENT OF C1
       END-EXEC.

When the statement runs, the cursor must be on a row or rowset. That row is deleted. The cursor then sits before the next row of its result; if there is no next row, it sits after the last row. A later FETCH without repositioning gets the next survivor. On a rowset cursor, WHERE CURRENT OF deletes the whole rowset; FOR ROW n OF ROWSET deletes one row and leaves the cursor on the rowset.

Host-language SQLCODE +100 after FETCH means no row; do not issue DELETE WHERE CURRENT OF in that state. After a successful positioned DELETE, do not assume the cursor still addresses the deleted RID.

DELETE temporal data

On a system-period temporal table, DELETE of a current row typically inserts the old image into the history table and removes it from the current table (row-end is stamped). Applications query “as of” with FOR SYSTEM_TIME. Deleting from history directly is not how the feature is meant to be used.

On an application-period table, FOR PORTION OF BUSINESS_TIME can delete only part of a row’s timeline. Db2 may keep leftover period fragments as remaining rows, similar in spirit to portion UPDATE. If CURRENT TEMPORAL BUSINESS_TIME is set and the bind option is sensitive, additional rules apply—coordinate with the temporal design, do not mix ad-hoc portion clauses with a non-null special register casually.

1
2
3
4
5
6
7
8
9
DELETE FROM HR.POLICY
 WHERE POLICY_ID = 'P100'
   AND BUS_START >= DATE('2020-01-01');
 
-- Portion delete (application-period table)
DELETE FROM HR.POLICY
  FOR PORTION OF BUSINESS_TIME
      FROM DATE('2024-01-01') TO DATE('2024-04-01')
 WHERE POLICY_ID = 'P100';

DELETE with triggers

A DELETE trigger fires when a DELETE statement removes a row (before and/or after, row-level or statement-level, depending how it was created). Triggers can enforce extra rules, write to an audit table, or SIGNAL SQLSTATE to abort. Cascaded RI deletes can fire triggers on dependent tables too. That is why a “simple” parent DELETE can become a large unit of work.

TRUNCATE is different: IGNORE DELETE TRIGGERS (the default) does not activate delete triggers; RESTRICT WHEN DELETE TRIGGERS errors if any delete trigger exists. Choose DELETE when the audit trigger must run.

DELETE with RI constraints

If the table is a parent in a referential constraint, the ON DELETE rule on the foreign key decides what happens to children:

RESTRICT is checked as soon as Db2 sees a dependent. NO ACTION waits until the end of the statement, so a single statement that deletes parent and children together can succeed under NO ACTION where RESTRICT would fail. SET NULL requires nullable foreign key columns. CASCADE can fan out: deleting one customer may delete orders, which cascade to order lines. Test with a small parent key before you mass-delete a parent table in production.

SQLCODE -532 (and related codes) appear when a delete is restricted by RI. Self- referencing tables (employee/manager) need an extra-careful delete order or a rule that does not cascade forever.

1
2
3
4
5
6
7
8
9
-- Child table definition (shape)
--   EMPNO CHAR(6) NOT NULL,
--   WORKDEPT CHAR(3),
--   FOREIGN KEY (WORKDEPT) REFERENCES DEPARTMENT(DEPTNO)
--     ON DELETE SET NULL
 
-- This parent delete nulls WORKDEPT on employees instead of failing
DELETE FROM HR.DEPARTMENT
 WHERE DEPTNO = 'E21';

Explain It Like I'm Five

DELETE is throwing a library card in the bin. WHERE is “only cards that are expired.” If you forget WHERE, every card goes in the bin. A cursor DELETE is “throw away the card in my hand.” Triggers are a librarian who writes in a notebook every time a card is thrown away. RI rules are family rules: CASCADE throws away the kids’ cards too, SET NULL keeps the kids but erases the family name, RESTRICT says you cannot throw the parent card away while kids still have cards.

Exercises

1. Write a searched DELETE that removes EMP_STAGE rows with STATUS = 'DONE' and LOAD_DATE older than 7 days.
2. Write a DELETE that uses NOT EXISTS to remove departments that have no employees. Why might RI still block it?
3. Describe cursor position after DELETE WHERE CURRENT OF C1 succeeds and a next row exists.
4. For ON DELETE CASCADE versus RESTRICT, give one business example where each is the better rule.
5. Explain one reason to use DELETE (not TRUNCATE) when emptying a work table that has an AFTER DELETE audit trigger.