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
- 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 |
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.