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 Temporal Table Support.
- INSERT/INSERT … SELECT
- UPDATE
- UPDATE (Upsert Form)
- “CREATE ERROR TABLE” and “HELP ERROR TABLE” in SQL Data Definition Language
- Temporal Table Support
- Database Administration
- Utilities
- Teradata FastLoad Reference
- Teradata MultiLoad Reference
- Teradata Parallel Data Pump Reference
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.
Syntax
