16.20 - MERGE - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Manipulation Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1146-162K

Purpose

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

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.

Syntax