MERGE Statement | SQL Statements | Teradata Vantage - MERGE - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
vjt1596846980081.ditamap
dita:ditavalPath
vjt1596846980081.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Merges a source row set into a primary-indexed target table based on whether any target rows satisfy a specified matching condition with the source row. The target table cannot be column partitioned.

IF the source and target rows … THEN the merge operation …
satisfy the matching condition updates based on the WHEN MATCHED THEN UPDATE clause.
deletes based on the WHEN MATCHED THEN DELETE clause.
do not satisfy the matching condition inserts based on the WHEN NOT MATCHED THEN INSERT clause.

For details on the temporal form of MERGE, see Teradata Vantage™ - Temporal Table Support, B035-1182

For more information, see:
  • INSERT/INSERT ... SELECT
  • UPDATE
  • UPDATE (Upsert Form)
  • CREATE ERROR TABLE and HELP ERROR TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144
  • Teradata Vantage™ - Temporal Table Support, B035-1182
  • Teradata Vantage™ - Database Administration, B035-1093
  • Teradata Vantage™ - Database Utilities, B035-1102
  • Teradata® FastLoad Reference, B035-2411
  • Teradata® MultiLoad Reference, B035-2409
  • Teradata® Parallel Data Pump Reference, B035-3021

ANSI Compliance

MERGE is ANSI SQL:2011-compliant.

Note that in the ANSI definition, this statement is named MERGE INTO, while in the Teradata definition, INTO is an optional keyword.

The exceptions to full ANSI compliance for the Teradata implementation of the MERGE statement are as follows:
  • The ANSI definition for this statement is MERGE INTO, while the Teradata definition is MERGE, with INTO being an optional keyword.
  • The Teradata implementation of MERGE does not support the ANSI OVERRIDE clause in the INSERT specification.

    In the ANSI definition of the MERGE statement, this clause applies to identity columns only and allows the overriding of either user-specified or system-generated identity values. Teradata does not support this operation in its regular non-error logging MERGE statements, either.

  • You cannot update or delete primary index column values using MERGE.
  • The match_condition you specify with the ON keyword must specify an equality constraint on the primary index of the target table. The target table cannot be a NoPI table or column-partitioned table.
    • Inequality conditions are not valid, nor are conditions specified on a column set other than the primary index column set for the target table.
    • The specified primary index value must match the primary index value implied by the column values specified in the INSERT clause.
    • match_condition cannot contain subqueries or references to columns that do not belong to either the source or target tables.
    • match_condition cannot equate explicitly with NULL.
    • If the primary index value is the result of an expression, then the expression cannot reference any column in the target table.
    • If the target table is a row-partitioned table, you must also specify the values of the partitioning columns in match_condition, and the INSERT clause must specify the same partitioning column values as match_condition.
  • For multiply-sourced rows in a MERGE operation, the firing sequence of triggers defined on the target table depends on the order of the UPDATE and INSERT components of the MERGE request. This can impact the results of the MERGE operation.
    If you specify the UPDATE component before the INSERT component, the order of processing is as follows:
    1. BEFORE UPDATE triggers
    2. BEFORE INSERT triggers
    3. MERGE UPDATE and MERGE INSERT operations
    4. AFTER UPDATE triggers
    5. AFTER INSERT triggers
    If you place the INSERT specification before the UPDATE specification, the order of processing is as follows:
    1. BEFORE INSERT triggers
    2. BEFORE UPDATE triggers
    3. MERGE INSERT and MERGE UPDATE operations
    4. AFTER INSERT triggers
    5. AFTER UPDATE triggers

Required Privileges

The privileges required to perform MERGE depend on the merge matching clause of the request you submit.

Merge Matching Clause Privilege Required
WHEN MATCHED UPDATE on every column of target_table that is specified in the UPDATE SET set clause list.

DELETE on every column of target_table that is specified in the DELETE SET set clause list.
WHEN NOT MATCHED INSERT on target_table.

The INSERT privilege is also required on all of the columns specified in the INSERT column list.
both
  • SELECT on any source table specified in a USING subquery.
  • all the update and insert privileges required for the WHEN MATCHED and WHEN NOT MATCHED clauses.

Note: DELETE cannot be combined with INSERT or UPDATE in a MERGE statement.

The privileges required for a MERGE LOGGING ERRORS operation are the same as those for MERGE operations without a LOGGING ERRORS option with the exception that you must also have the INSERT privilege on the error table associated with the target data table for the MERGE operation.