MERGE UPDATE Duplicate Rows - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

When MERGE detects duplicate rows during an UPDATE operation, the actions Teradata 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, Teradata 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, Teradata 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, Teradata 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, Teradata Database aborts and rolls back the request.
  • The following rule sets apply to different aspects of the MERGE statement.