Rules for Target Tables in a MERGE Statement - 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

Following are the rules for target tables in a MERGE statement.

Only target table rows that existed before the MERGE statement began its execution are candidates for being updated. Rows inserted into the target table after the MERGE request begins its execution cannot be updated until after that MERGE request stops executing.

The target relation in a MERGE operation can be any of the following types of relation:

  • Base data table
  • Global temporary table
  • Hashed table
  • Queue table
  • Updatable (single‑table) view
  • Volatile table
  • The target relation of a MERGE operation cannot be a joined table, or nonupdatable, view. For example, you cannot specify a view like the following as the target relation in a MERGE operation:

         CREATE VIEW v (a, b, c) AS 
           SELECT a2, b2, c2 
           FROM t2 INNER JOIN t1 ON a1=a2;

    The target relation of a MERGE operation cannot be any of the following types of relation:

  • Derived table
  • Global temporary trace table
  • Hash index
  • Joined table (nonupdatable) view
  • Join index
  • Journal table
  • When the target table is a row‑partitioned table, you cannot substitute the system‑derived PARTITION column or any of the system‑derived PARTITION#Ln columns, for the partitioning column set in the ON condition.

    You can include the system‑derived PARTITION column in the ON clause predicate as a secondary condition.

    The target table in a MERGE operation can have an identity column. See “Identity Columns” in SQL Data Definition Language Detailed Topics. The system generates numbers for MERGE inserts into the identity column in the same way it performs singleton inserts into tables that do not have an identity column.

    However, the identity column cannot be the primary index for the target table under most circumstances. For a list of rules and exceptions that apply to primary index identity columns, see “Primary Index of the Target Table” on page 427.

    You cannot specify target table columns in the INSERT specification of a WHEN NOT MATCHED THEN clause.