The MERGE statement in DB2 for z/OS

MERGE

IBM’s picture is a right join of the target to the source using ON. Where that join is true, the MATCHED action runs. Where it is not true, the NOT MATCHED insert runs. Matching conditions are evaluated in the order you write them; once a row satisfies a WHEN (when NOT ATOMIC is not used), later WHEN clauses do not see it again.

1
2
3
4
5
6
7
8
MERGE INTO RECORDS AR
 USING (SELECT ACTIVITY, DESCRIPTION FROM ACTIVITIES) AC
    ON (AR.ACTIVITY = AC.ACTIVITY)
 WHEN MATCHED THEN
      UPDATE SET DESCRIPTION = AC.DESCRIPTION
 WHEN NOT MATCHED THEN
      INSERT (ACTIVITY, DESCRIPTION)
      VALUES (AC.ACTIVITY, AC.DESCRIPTION);

That IBM example updates descriptions that already exist and inserts activities that do not. Correlation names AR and AC keep column names unambiguous. Qualify every key in ON when the same name exists on both sides.

MERGE source tables, queries, and VALUES

A table-reference can be a base table, a view, or a nested fullselect. If that result is empty, Db2 returns a warning and changes nothing. Do not specify NOT ATOMIC with a table-reference. Do not mix table-reference and VALUES in the same MERGE.

1
2
3
4
5
6
7
MERGE INTO T1 AS A
 USING TABLE (VALUES ('rsk', 3)) AS T (ID, AMOUNT)
    ON A.COL1 = T.ID
 WHEN MATCHED THEN
      UPDATE SET COL2 = T.AMOUNT
 WHEN NOT MATCHED THEN
      INSERT (COL1, COL2) VALUES (T.ID, T.AMOUNT);

Multiple-row VALUES and host-variable arrays are the embedded-SQL path for a batch of incoming rows. FOR n ROWS says how many array slots to merge (1–32767). The number of VALUES items must not exceed 750. Expressions in VALUES must not be column names of the target and must not use NEXT VALUE / PREVIOUS VALUE.

MERGE predicates (ON and AND)

ON is the match rule. Each column name must belong to the target or the source. Subqueries are not allowed in ON. IN and quantified predicates must not include a fullselect. Aggregate functions and non-deterministic scalar functions are also out. Keep ON as an equality (or a small AND of equalities) on keys.

WHEN MATCHED AND extra-condition lets you split matches: for example UPDATE when status is active, DELETE when status is cancelled. WHEN NOT MATCHED AND extra- condition filters inserts; that extra condition must not reference target columns.

1
2
3
4
5
6
7
8
9
10
11
MERGE INTO HR.EMPLOYEE E
 USING HR.EMP_INBOUND I
    ON E.EMPNO = I.EMPNO
 WHEN MATCHED AND I.ACTION = 'UPD' THEN
      UPDATE SET SALARY = I.SALARY,
                 BONUS  = I.BONUS
 WHEN MATCHED AND I.ACTION = 'DEL' THEN
      DELETE
 WHEN NOT MATCHED AND I.ACTION = 'ADD' THEN
      INSERT (EMPNO, LASTNAME, SALARY, BONUS)
      VALUES (I.EMPNO, I.LASTNAME, I.SALARY, I.BONUS);

MERGE UPDATE

WHEN MATCHED THEN UPDATE SET looks like searched UPDATE assignment: expressions, DEFAULT, NULL, or a row-fullselect. Expressions can read target and source columns; the target column value is the value before this row is updated. Do not assign generated ALWAYS columns except DEFAULT. The same target column must not appear twice in one SET. With VALUES sources, later source rows can update a row already updated earlier in the same MERGE (cumulative).

MERGE INSERT

WHEN NOT MATCHED THEN INSERT lists columns and VALUES from the source. Omitted columns get defaults (or null). GENERATED ALWAYS columns should receive DEFAULT. INSERT must not reference a column of the target table—the row does not exist yet. Views that are not insertable need an explicit column list that skips non-updatable columns.

MERGE DELETE

WHEN MATCHED THEN DELETE removes the matching target row. RI, delete triggers, and temporal history behavior are those of DELETE. Use DELETE in MERGE when the source is a “cancel this key” feed rather than running a separate DELETE first.

MERGE restrictions

• Illegal targets include catalog/directory tables, created GTTs, read-only views, system-maintained MQTs, implicit XML tables, accelerator-only tables, and views with INSTEAD OF triggers.
• ON cannot contain subqueries, fullselect IN lists, quantified fullselects, aggregates, or non-deterministic functions.
• Source type versus NOT ATOMIC — table-reference and NOT ATOMIC are mutually exclusive; multi-row VALUES requires NOT ATOMIC CONTINUE ON SQLEXCEPTION.
• One MATCHED / one NOT MATCHED when NOT ATOMIC or VALUES is used.
• Cardinality — if one source row matches several target rows, or several source rows match one target, you must understand the defined behavior and unique keys. Design ON so a source key matches at most one target row unless you intend multi-row updates.
• Triggers and RI still fire for the INSERT/UPDATE/DELETE that MERGE performs.
• ELSE IGNORE (when present in your function level) skips source rows that hit no WHEN clause instead of failing.

MERGE is not a replacement for LOAD or for unconstrained mass UPDATE. It shines when a modest source set must be applied to a keyed target with mixed insert/update/delete outcomes in one atomic (or NOT ATOMIC) statement.

Explain It Like I'm Five

You have a binder of class records (the target) and a pile of new slips (the source). For each slip, you look up the student number. If the student is already in the binder, you erase and rewrite the line (UPDATE) or pull the page out (DELETE). If the number is new, you add a page (INSERT). ON is “same student number.” MATCHED means you found them. NOT MATCHED means you did not.

Exercises

1. Write a MERGE that updates SALARY when EMPNO matches and inserts otherwise, using a SELECT from HR.EMP_INBOUND as the source.
2. Add WHEN MATCHED AND ACTION = 'DEL' THEN DELETE. Why must ACTION live on the source, not only the target?
3. List two reasons ON (SELECT …) would be rejected.
4. When would you choose a table-reference source instead of host-variable array VALUES?
5. Explain why GENERATED ALWAYS identity columns belong in INSERT VALUES as DEFAULT (or omitted) rather than as a source number you made up.