Usage Notes - 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

Exceptions to Full ANSI Compliance

The five exceptions to full ANSI compliance for the Teradata implementation of the MERGE statement are as follows:

  • The ANSI definition for this statement is MERGE INTO, while the Teradata definition is MERGE, with INTO being an optional keyword.
  • The Teradata implementation of MERGE does not support the ANSI OVERRIDE clause in the INSERT specification.
  • In the ANSI definition of the MERGE statement, this clause applies to identity columns only and allows the overriding of either user‑specified or system‑generated identity values. Teradata does not support this operation in its regular non‑error logging MERGE statements, either.

  • You cannot update or delete primary index column values using MERGE.
  • The match_condition you specify with the ON keyword must specify an equality constraint on the primary index of the target table. The target table cannot be a NoPI table or column-partitioned table.
  • Inequality conditions are not valid, nor are conditions specified on a column set other than the primary index column set for the target table.
  • The specified primary index value must match the primary index value implied by the column values specified in the INSERT clause.
  • match_condition cannot contain subqueries or references to columns that do not belong to either the source or target tables.
  • match_condition cannot equate explicitly with NULL.
  • If the primary index value is the result of an expression, then the expression cannot reference any column in the target table.
  • If the target table is a row-partitioned table, you must also specify the values of the partitioning columns in match_condition, and the INSERT clause must specify the same partitioning column values as match_condition.
  • For multiply-sourced rows in a MERGE operation, the firing sequence of triggers defined on the target table depends on the order of the UPDATE and INSERT components of the MERGE request. This can impact the results of the MERGE operation.
  • If you specify the UPDATE component before the INSERT component, the order of processing is as follows:

    a BEFORE UPDATE triggers

    b BEFORE INSERT triggers

    c MERGE UPDATE and MERGE INSERT operations

    d AFTER UPDATE triggers

    e AFTER INSERT triggers

    If you place the INSERT specification before the UPDATE specification, the order of processing is as follows:

    a BEFORE INSERT triggers

    b BEFORE UPDATE triggers

    c MERGE INSERT and MERGE UPDATE operations

    d AFTER INSERT triggers

    e AFTER UPDATE triggers

    The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT operation or within a MERGE request.

     

    Transaction Isolation Level

    DBS Control AccessLockForUncomRead Field Setting

    Default Locking Severity for Outer SELECT and Ordinary SELECT Subquery Operations

    Default Locking Severity for SELECT Operations Embedded Within a MERGE Request

    SERIALIZABLE

    FALSE

    READ

    READ

    TRUE

    READ

    READ UNCOMMITTED

     

    FALSE

    READ

    TRUE

    ACCESS

    MERGE requests are also affected by the locking levels set by you or the system. The default locking for MERGE requests is as follows.

  • Table‑level WRITE locks on the target table. For a nonconcurrent isolated merge on a load isolated table, the merge operation sets an EXCLUSIVE lock on the target table.
  • READ or ACCESS locks on the source table depending on the situation and whether you specify a LOCKING request modifier.
  • The following cases illustrate the effect of these locking levels.