17.05 - About the MERGE Statement - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
The MERGE statement combines the UPDATE and INSERT statements into a single statement with two conditional test clauses:
  • WHEN MATCHED, UPDATE.
  • WHEN NOT MATCHED, INSERT.

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:

Clause Evaluates to True MERGE Description
WHEN MATCHED Updates a matching target table row with the set of values taken from the current source row.

Deletes a matching target table row.

WHEN NOT MATCHED Inserts the current source row into the target table.

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.

The Merge with Matched Updates and Unmatched Inserts AMP step performs inserts and updates in a single step. A merge with matched updates and unmatched inserts step can perform any of the following operations:
  • INSERT only
  • UPDATE only
  • INSERT and UPDATE

A merge with matched deletes performs a DELETE only.

The merge with matched updates and unmatched inserts step assumes that the source table is always 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, after which it performs those updates and inserts.

This step is very similar to the APPLY phase of MultiLoad because it 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 should be inserted into the target table or whether a matching target table row should be updated with the value set taken from the source varies accordingly. Note that 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 Venn diagram below 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.



This description uses the following terms:
  • The target_table variable specifies the target table being modified.
  • The correlation_name variable is an optional alias for the target table.
  • The source_table_name variable following the USING keyword specifies the source table whose rows act as the source for update or delete and insert operations.
  • The match_condition variable following the ON keyword divides the source table into a set of rows that match rows in the target table and a set of rows that do not match rows in the target table.

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 executes, the system activates triggers defined on UPDATE or DELETE operations.

When a MERGE statement with a MERGE WHEN NOT MATCHED clause executes, 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 executes 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 might result in referential actions being executed.
  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 executes 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 might result in referential actions being executed.
  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 in the same way that the UPDATE statement does, and it processes duplicate INSERT rows in the same way that an INSERT … SELECT request does, as indicated by the following bullets.

MERGE INSERT Duplicate Rows

When MERGE detects duplicate rows during an INSERT operation, the database takes different actions on the request depending on several factors.
  • If no error logging is specified for the request AND the target table is a SET table AND the session is in ANSI session mode, then the request aborts and rolls back.
  • If error logging is specified for the request AND the target table is a SET table AND the session is in ANSI session mode, then the request aborts and rolls back if there are any nonlocal errors, but only after the MERGE request completes or the specified error limit is reached.

    Error-causing rows are logged in an error table and are not rolled back.

  • If the target table is a SET table AND the session is in Teradata session mode, then any duplicate rows are silently ignored.

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:

IN this session mode … The system handles duplicate row insert attempts by …
ANSI not inserting them into the target table.

It logs them as errors in the appropriate error table.

The system inserts no rows into the target table under these circumstances.

Teradata inserting the first row of the duplicate set into the target table and rejecting all the remaining rows from that set without logging them as errors.

The system inserts one row into the target table under these circumstances.

Unlike an INSERT … SELECT statement, MERGE does not silently ignore duplicate row insert attempts into a SET table in Teradata session mode.

MERGE UPDATE Duplicate Rows

When MERGE detects duplicate rows during an UPDATE operation, the action the database performs depends on several factors. Duplicate row processing for UPDATE operations is the same in ANSI or Teradata session mode.
  • If no error logging is specified for the request AND the target table is a SET table, then the request aborts and rolls back.
  • If error logging is specified for the request AND the target table is a SET table, the request aborts and rolls back if there are any nonlocal errors, but only after the MERGE request completes or the specified error limit is reached.

    Error-causing rows are logged in an error table and are not rolled back.

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:

IN this session mode … The system processes duplicate update attempts by …
ANSI taking one of the following actions:
  • If error logging is enabled, the database logs each duplicate update attempt as an error in the appropriate error table.

    This means that the system updates the target table row only once under these circumstances.

  • If error logging is not enabled, the database aborts and rolls back the request.
Teradata updating the row from the duplicate set the first time and rejecting all the remaining update attempts from that set.

The system updates only one row in the target table under these circumstances.

  • If error logging is enabled, the database logs each duplicate update attempt as an error in the appropriate error table.

    This means that the system updates the target table row only once under these circumstances.

  • If error logging is not enabled, the database aborts and rolls back the request.