MERGE Statement - Teradata VantageCloud Lake

You can also use the MERGE statement to delete rows by specifying: WHEN MATCHED, DELETE.

The following table explains the meaning of these conditional clauses:

A MERGE statement can specify one WHEN MATCHED clause and one WHEN NOT MATCHED clause, in either order. You need not specify both. However, you must specify at least one of these clauses.

A merge with matched deletes performs a DELETE only.

The merge with matched updates and unmatched inserts step assumes that the source table is distributed on the join column of the source table, which is specified in the ON clause as an equality constraint with the primary index of the target table and sorted on the RowKey.

The step does a RowKey-based Merge Join internally, identifying source rows that qualify for updating target rows and source rows that qualify for inserts, and then performs those updates and inserts.

Like the APPLY phase of MultiLoad, this step guarantees that the target table data block is read and written only once during the MERGE operation.

The order of evaluating whether a source row must be inserted into the target table or whether a matching target table row must be updated with the value set taken from the source varies accordingly. The result depends on the order in which you specify the two clauses.

MERGE Statement Processing

This section describes the general actions performed by a MERGE statement:

The following Venn diagram divides the source table rows into two disjunct sets: set A and set B. Set A is the set of matching rows and set B is the set of nonmatching rows.

The set of matching rows, A, defines the update or delete source, or staging, table.

The set of nonmatching rows, B, defines the insert source, or staging, table.

Either set can be empty, but both cannot be empty for a MERGE operation.

The system uses the set of matching rows as the update or delete source table for the update operation, as specified by the update_column=update_expression variable.

The system uses the set of nonmatching rows as the insert source table for the insert operation, as specified by the insert_expression variable.

MERGE with Triggers

When a MERGE statement with a MERGE WHEN MATCHED clause runs, the system activates triggers defined on UPDATE or DELETE operations.

When a MERGE statement with a MERGE WHEN NOT MATCHED clause runs, the system activates triggers defined on INSERT operations.

The order of activation of UPDATE and INSERT triggers is the same as the order of the MERGE WHEN MATCHED and MERGE WHEN NOT MATCHED clauses in the MERGE request.

The following orders of operations apply to MERGE operations on source tables that have multiple rows. Assume for all cases that both updates to existing rows in the target table and inserts of new rows into the target table occur without incident.

MERGE Order of Operations

The order of operations depends on whether you specify the MERGE WHEN MATCHED (update) clause first or the MERGE WHEN NOT MATCHED (insert) clause first as follows.

MERGE WHEN MATCHED (Update) First

The sequence of actions when the MERGE statement runs are as follows:

1. All BEFORE triggers associated with UPDATE actions are applied.
2. All BEFORE triggers associated with INSERT actions are applied.
3. The UPDATE operations specified by the MERGE update specification and the INSERT operations specified by the MERGE insert specification are applied.
4. The system checks any specified constraints, which may cause referential actions.
5. All AFTER triggers associated with UPDATE actions are applied.
6. All AFTER triggers associated with INSERT actions are applied.

MERGE WHEN NOT MATCHED (Insert) First

The sequence of actions when the MERGE statement runs are as follows.

1. All BEFORE triggers associated with INSERT actions are applied.
2. All BEFORE triggers associated with UPDATE actions are applied.
3. The INSERT operations specified by the MERGE insert specification and the UPDATE operations specified by the MERGE update specification are applied.
4. The system checks any specified constraints, which may cause referential actions.
5. All AFTER triggers associated with INSERT actions are applied.
6. All AFTER triggers associated with UPDATE actions are applied.

MERGE with Duplicate Rows

MERGE processes duplicate UPDATE rows as the UPDATE statement does, and processes duplicate INSERT rows as an INSERT ... SELECT request does, as the following sections explain.

MERGE INSERT Duplicate Rows

The INSERT source relation for a MERGE statement can contain duplicate rows.

Like the case for an INSERT ... SELECT statement, MERGE silently ignores duplicate row INSERT attempts into a SET table in Teradata session mode.

When the system inserts rows into the target table, the insertion of duplicate rows is governed by the normal constraint check rules enforced by the session mode types:

MERGE does not silently ignore duplicate row insert attempts into a SET table in Teradata session mode.

MERGE UPDATE Duplicate Rows

The UPDATE source relation for a MERGE statement can contain duplicate rows.

When the system updates rows in the target table, duplicate updates are processed as described in the following table: