17.05 - Rules and Limitations for MERGE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following rule sets apply to different aspects of the MERGE statement.

Single Row MERGE Rules and Limitations

The following items define the meaning of single row with respect to source table rules and limitations for MERGE requests:
  • The specified match_condition must specify a single primary index for the target row.
  • If you specify a subquery for source_table_reference, then that subquery must be a singleton SELECT (that is, it cannot retrieve more than a single row from the referenced table) and it must specify either a UPI or USI to make the retrieval. The selected columns must be referenced explicitly: the SELECT * syntax is not permitted.
  • If you specify a simple list of column values for source_table_reference, then the retrieval, by definition, retrieves only a single row.

Rules for MERGE Request ON Clauses

A MERGE request ON clause has:
  • One, and only one, primary condition
  • Zero, one, or many secondary conditions

A primary condition is the necessary condition for the MERGE request to execute, while secondary conditions are optional and are not required unless the application requires them.

Following are examples of primary and secondary ON clause conditions.

Suppose you create the following two tables and then submit the MERGE request that follows their definition.

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER);
     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER);

     MERGE INTO t1
     USING t2
       ON a1=b2
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

The predicate ON a1 = b2 is the primary, and only, condition in this MERGE request.

If the ON clause is modified to ON a1=b2 AND c1=c2, then c1=c2 is a secondary condition.

Row-Partitioned Tables Primary Condition Definition

For row-partitioned tables, the partitioning column set must be a component of the primary condition definition. For example, consider the MERGE request for the following target and source table definitions:

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER)
     PRIMARY INDEX (a1)
     PARTITION BY (c1);

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER);

     MERGE INTO t1
     USING t2
       ON a1=b2 AND c1=10 AND b1<b2
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

For this MERGE request, the primary condition is a1=b2 AND c1=10, and b1<b2 is a secondary condition.

Rules for ON Clause Definitions

The following set of rules clarifies these ON clause definitions.

Rules for Primary Condition in the ON Clause

The primary condition in the ON clause must be an equality constraint. This is the minimum condition required for a valid ON clause predicate. Secondary conditions do not have this restriction.

For example, the following MERGE request is not valid because the primary ON clause condition, a1<a2, is not an equality constraint.

     MERGE INTO t1
     USING t2
       ON  a1<a2 
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

The following MERGE request is valid because the primary condition in its ON clause, a1=a2, is an equality constraint. The secondary condition b1<>b2 being a nonequality has no bearing on the validity of the request because any Θ operator is valid for a secondary condition.

     USING t2
     MERGE INTO t1
       ON  a1=a2  AND b1<>b2
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

Restrictions for Primary Condition in the ON clause

The primary condition in the ON clause must not specify an expression on any of the following:
  • Target table primary index
  • Target table partitioning expression
  • Both the target table primary index and its partitioning expression

Consider the following target and source table definitions:

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER)
     PRIMARY INDEX (a1)
     PARTITION BY (c1);

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER);

The following MERGE request is not valid because the primary condition in the ON clause specifies the expressions a1+10 and c1*b1 on the primary index a1, and the partitioning column c1,of target table t1, respectively.

     MERGE INTO t1
     USING t2
       ON  a1+10=b2 AND c1*b1=10  AND b1<b2
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

However if the primary index, or the partitioning column set, or both are specified in a secondary condition, this restriction does not apply, as is demonstrated by the following valid example:

     MERGE INTO t1
     USING t2
       ON a1=b2 AND c1=10 AND  a1+10=c2 AND c1*b1=10 
     WHEN MATCHED THEN
       UPDATE SET b1=b2;

In this MERGE request, the ON condition expressions a1+10=c2 and c1*b1=10 are specified in a secondary condition, so the request is valid.

Primary Condition in ON Clause Must be Equality Condition

The primary condition in the ON clause must specify an equality condition with the primary index of the target table, and with its partitioning column if it is a row-partitioned table. The expression must also be identical to the expression specified for the primary index and partitioning column in the INSERT specification. The specified primary index value must equal the primary index value (and partitioning column value if the target table has row partitioning) implied by the column values specified in the INSERT specification of the WHEN NOT MATCHED clause.

Primary Index Value Results from an Expression

If the primary index value results from the evaluation of an expression, then that expression cannot reference any column in the target table.

Consider the following target and source table definitions:

     CREATE TABLE t1 (
       x1 INTEGER,
       y1 INTEGER,
       z1 INTEGER)
     PRIMARY INDEX(x1,y1);

     CREATE TABLE t2 (
       x2 INTEGER,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX(x2,y2);

The following MERGE request is valid because it specifies equality conditions on each of the primary index columns of target table t1, the columns x1 and y1:

     MERGE INTO t1
     USING t2
       ON  x1=z2 AND y1=y2 
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (z2, y2, x2);

The following MERGE request is valid because it specifies equality conditions on each of the primary index columns of target table t1 and because the expressions on the RHS of those conditions, z2+10 and y2+20, are also specified for the primary index columns of t1 in the INSERT specification of the request:

     MERGE INTO t1
     USING t2
       ON  x1=z2+10 AND y1=y2+20 
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (x1, y1, z1) VALUES (z2+10,  y2+20, x2);

Target Table with Row Partitioning

For a target table with row partitioning, the MERGE request ON clause must specify a condition on the partitioning column and its WHEN NOT MATCHED clause must match that condition.

Consider the following target and source table definitions:

     CREATE TABLE t1 (
       x1 INTEGER,
       y1 INTEGER,
       z1 INTEGER)
     PRIMARY INDEX (x1)
     PARTITION BY y1;

     CREATE TABLE t2 (
       x2 INTEGER,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX (x2);

The following MERGE request is valid because it specifies a condition on the partitioning column of t1, y1=y2, and its INSERT specification inserts a value from column t2.y2 into column t1.y1:

     MERGE INTO t1
     USING (SELECT *
            FROM t2) AS s
       ON x1=x2 AND  y1=y2 
     WHEN MATCHED THEN
       UPDATE SET z1=z2
     WHEN NOT MATCHED THEN
       INSERT (x2,  y2, z2);

The following MERGE request is not valid because while its ON clause specifies a valid condition of x1=z2 AND y1=y2, its INSERT specification inserts a value from column t2.y2 into column t1.x1, which does not match the ON clause condition.

     MERGE INTO t1
     USING t2
       ON x1=z2 AND y1=y2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (x1, y1, z1) VALUES (y2, z2, x2);

The following MERGE request is not valid because while its ON clause specifies a valid condition of x1=z2+10, its INSERT specification inserts the value for the expression t2.y2 + 20 into t1.x1, which does not match the ON clause condition.

     MERGE INTO t1
     USING t2
       ON  x1=z2+10  AND y1=y2+20
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2+20, z2+10, x2);

Primary Condition Must Be ANDed with Secondary Conditions

The primary condition must be conjunctive (ANDed) with any secondary conditions specified in the ON clause. The secondary condition terms can be disjunctive (ORed) among themselves, but not with the primary condition.

For example, consider the following target and source table definitions:

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER);

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER);

The following MERGE request is valid because the target table primary index equality expression a1=a2, which is the primary condition, is ANDed with the secondary condition b1=b2.

     MERGE INTO t1
     USING t2
       ON a1=a2  AND  b1=b2
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

The following MERGE request is not valid because the target table primary index equality expression a1=a2 is ORed with the secondary condition b1=b2.

     MERGE INTO t1
     USING t2
       ON a1=a2  OR  b1=b2
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

However, the ON clause secondary conditions can contain ORed terms.

For example, the following MERGE request is valid because its primary condition a1=a2 is ANDed with the secondary condition (b1=b2 OR c1=c2), and the disjunction is contained entirely within the secondary condition:

     MERGE INTO t1
     USING t2
       ON  a1=a2 AND (b1=b2 OR c1=c2) 
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

MERGE ON Deterministic and Nondeterministic Functions

The rules for specifying deterministic and nondeterministic functions in a MERGE statement ON clause differ for primary and secondary conditions.

The section describes restrictions for specifying deterministic and nondeterministic functions in the primary condition of an ON clause.

MERGE ON Deterministic Functions in the Primary Condition

There are no restrictions on the specification of deterministic functions in the primary condition of a MERGE statement ON clause.

For example, the following MERGE request is valid:

     MERGE INTO t1
     USING t2
       ON a1=deterministic_udf(b2) AND b1=b2
     WHEN MATCHED THEN
       UPDATE SET c1=c2
     WHEN NOT MATCHED THEN
       INSERT (deterministic_udf(b2), a2, c2);

The deterministic UDF expression deterministic_udf(b2) executes only once for each row in the source table while spooling the source table t2. It is not executed again while the system processes the INSERT specification, but the value computed in the source spool is used in its place. This is a specific performance optimization and saves significant CPU time if the function is complicated.

MERGE ON Nondeterministic Functions in the Primary Condition

You cannot specify nondeterministic functions in the primary condition of the ON clause because even when the ON clause expression matches the primary index value in the INSERT specification, the evaluation of the UDF might be different when it is executed in the context of the ON clause than when it is executed in the context of the INSERT specification, leading to an unreasonable insert.

In this context, an unreasonable insert is an insert operation that causes a nonlocal AMP insert. In other words, the row might need to be redistributed to a different AMP before it could be inserted into the target table.

For example, the following MERGE request is not valid because it specifies a nondeterministic function as the primary condition in the ON clause, even though that condition matches the INSERT specification:

     MERGE INTO t1
     USING t2
       ON  a1=non_deterministic_udf(b2) 
     WHEN MATCHED THEN
       UPDATE SET b1=b2
     WHEN NOT MATCHED THEN
       INSERT (non_deterministic_udf(b2), a2, c2);

A parallel example that specifies the RANDOM function as the primary condition in the ON clause and matches the INSERT specification is also nondeterministic and, therefore, not valid. For more information about the RANDOM function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

     MERGE INTO t1
     USING t2
       ON  a1=RANDOM (1,100) 
     WHEN MATCHED THEN
       UPDATE SET b1=b2
     WHEN NOT MATCHED THEN
       INSERT (RANDOM (1,100), b2, c2);

To avoid this problem, you should always specify the appropriate DETERMINISTIC or NOT DETERMINISTIC option for the CREATE FUNCTION statement when you create your external UDFs. For details, see CREATE FUNCTION and CREATE FUNCTION (Table Form) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 . This assists the Parser to take the actions necessary to process the MERGE request properly. If the external UDF is specified as DETERMINISTIC even though its behavior is NOT DETERMINISTIC, its execution can cause an internal AMP error during processing of the request, causing it to abort.

MERGE ON Deterministic and Nondeterministic Functions in Secondary Conditions

The restrictions for specifying deterministic and nondeterministic functions in the secondary conditions of an ON clause are as follows:

There are no restrictions regarding either deterministic or nondeterministic functions that are specified in the secondary conditions of a MERGE request ON clause.

For example, the following case is a valid MERGE request because the deterministic_udf and non_deterministic_udf functions are specified in the ON clause as secondary conditions, which is valid.

     MERGE INTO t1
     USING t2
       ON a1=a2  AND b1=deterministic_udf(b2) 
                AND c1=non_deterministic_udf(c2) 
     WHEN MATCHED THEN
       UPDATE SET b1=b2
     WHEN NOT MATCHED THEN
       INSERT (a2, deterministic_udf(b2),
               non_deterministic_udf(c2));

The RANDOM function is nondeterministic by definition. Therefore, the restrictions that apply to nondeterministic UDFs apply equally to RANDOM.

The following MERGE request is valid because it specifies a RANDOM function in a secondary condition of its ON clause.

     MERGE INTO t1
     USING t2
       ON a1=a2 AND  b1=RANDOM(1,100) 
     WHEN MATCHED THEN
       UPDATE SET b1=b2
     WHEN NOT MATCHED THEN
       INSERT (a2, RANDOM(1,100), c2);

Target Table Primary Index and Partitioning Column

The following rules apply to updating the primary index and partitioning column of a target table:

You cannot update the primary index or partitioning column of the target table.

Consider the following target and source table definitions:

     CREATE TABLE t1 (
       x1 INTEGER,
       y1 INTEGER,
       z1 INTEGER);

     CREATE TABLE t2 (
       x2 INTEGER,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX(x2)
     UNIQUE INDEX (y2);

The following MERGE request is valid because the source relation is a single row due to its WHERE clause specifying a constant for the USI column y2:

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON  x1=10  AND y1=20
     WHEN MATCHED THEN
       UPDATE SET x1=10 
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

The following MERGE request is not valid because even though the source relation s (derived from t2) is a single row, the ON clause does not specify a constant condition, which violates ON clause rule 2, and the primary index of target table t1, x1, is updated, which violates the nonupdatability rule on primary index and partitioning column expressions.

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON x1=y2 AND y1=z2
     WHEN MATCHED THEN
       UPDATE SET x1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

Primary Index of the Target Table

The primary index of the target table cannot be an identity column if you stipulate an INSERT specification and the ON clause predicate specifies an equality condition with the target table primary index (and with its partitioning column if it has row partitioning), and the expression specifies only source table columns.

Following are exceptions to this rule:
  • The source relation is a valid single-row subquery.
  • The request does not specify an INSERT specification and the primary condition in the ON clause is an equality constraint.
  • You do not specify an INSERT clause if the MERGE request has an equality condition with the primary index of the target table (and partition column set, if the target table has row partitioning).

See Example: MERGE and Identity Columns for three valid examples.

Consider the following target and source table definitions. Note that target table t1 defines an identity column on its default primary index, which is column x1.

     CREATE TABLE t1 (
       x1 INTEGER GENERATED BY DEFAULT AS IDENTITY,
       y1 INTEGER,
       z1 INTEGER);

     CREATE TABLE t2 (x2 INT,
       y2 INTEGER,
       z2 INTEGER)
     PRIMARY INDEX (x2)
     UNIQUE INDEX (y2);

The following MERGE request is valid because its source relation s, based on a projection of t2, is a valid single-row subquery:

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON x1=10 AND y1=20
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

For the following MERGE request, if a constant is not specified, you would have to follow ON clause rule 1 for the case where a WHEN NOT MATCHED clause is not specified, which would render the request to be nonvalid. However, this example specifies the constant value 10 in the WHEN MATCHED clause, so it is valid.

     MERGE INTO t1
     USING (SELECT x2, y2, z2
            FROM t2
            WHERE y2=10) AS s
       ON x1=y2 AND y1=z2
     WHEN MATCHED THEN
       UPDATE SET  z1=10;

MERGE Columns Must Reference Source or Target Tables

You can only specify columns that reference the source or target tables for the MERGE request in the ON, WHEN MATCHED, or WHEN NOT MATCHED clauses.

The following MERGE request is not valid because it references a column in its ON clause, t4.x4, that is neither from the source nor the target table for the request.

     MERGE INTO t1
     USING (SELECT x2, y2, z3
            FROM t2, t3
            WHERE y2=10) AS s
       ON x1=y2 AND  t4.x4=z2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

The following MERGE request is not valid because it references a table and column in its ON clause, t3.x4, that are neither the source nor the target for the request.

     MERGE INTO t1
     USING (SELECT x2, y2, z3
            FROM t2, t3
            WHERE y2=10) AS s
       ON x1=y2 AND  t3.x4=z2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (y2, z2, x2);

ON Clause Conditions

ON clause conditions have the same restrictions as join conditions with the additional restriction that an ON clause cannot specify a subquery.

The following MERGE request is not valid because it specifies a subquery in its ON clause:

     MERGE INTO t1
     USING t2
       ON a1=a2 AND c1 IN  (SELECT b2 
                           FROM   t2) 
     WHEN MATCHED THEN
       UPDATE SET c1=c2+2;

The following MERGE request is not valid because it specifies an aggregate operator, SUM, in its ON clause:

     MERGE INTO t1
     USING t2
       ON a1=a2 AND  SUM(b1)=10 
     WHEN NOT MATCHED THEN
       INSERT (a2,b2,c2);

Rules for Source Tables in a MERGE Statement

If the source relation is defined by a subquery, the subquery must conform to the restrictions for derived tables. See Rules and Restrictions for Derived Tables. You cannot specifying any of the following SQL elements:
  • ORDER BY
  • recursion
  • WITH
  • WITH … BY

The following MERGE request is not valid because it specifies a WITH … BY clause in its source relation subquery:

     MERGE INTO t1
     USING (SELECT a2, b2, c2
            FROM t2
            WITH SUM(b2) BY c2) AS source (a2, b2, c2)
       ON a1=a2
     WHEN MATCHED THEN
       UPDATE SET c1=c2;

The following MERGE request is not valid because it specifies an ORDER BY clause in its source relation subquery:

     MERGE INTO t1
     USING (SELECT a2, b2, c2
            FROM t2
            ORDER BY b2) AS source (a2, b2, c2)
       ON a1=a2
     WHEN NOT MATCHED THEN
       INSERT (a2,b2,c2);

If you specify the source table using a subquery, then the select list of that subquery cannot reference columns from the derived table it creates.

If the UPDATE source table contains more than one matching row for any given row in the target table, it is an error.

For example, suppose you have defined the following target and source tables:

     CREATE TABLE target (
       a INTEGER,
       b INTEGER);

     CREATE TABLE source (
       c INTEGER,
       d INTEGER);

You then populate the tables using the following INSERT requests:

     INSERT INTO target VALUES (1,1);

     INSERT INTO source VALUES (1,2);

     INSERT INTO source VALUES (1,3);

Next you perform the following MERGE request:

     MERGE INTO target AS t
     USING (SELECT c, d
            FROM source) AS s
       ON t.a=s.c
     WHEN MATCHED THEN
       UPDATE SET b=s.d;

Assume that the target table looks like this before it is updated. Note that the values for column d are ordered differently for version 1 and version 2 of the source table:

source - version 1   target
c d   a b
1 2   1 1
1 3      
source - version 2  
c d
1 3
1 2

The outcome of this MERGE update operation depends on the order of the rows in source. For example, compare the result of the update on target using version 1 of source versus the result of the update on target using version 2 of source.

Using version 1:

target  
a b
1 2

Using version 2:

target  
a b
1 3

As you can see, depending on the order of the rows in source, the final value for column b in target can be either 2 or 3, proof of nondeterministic behavior.

This is an error condition.

To avoid this error, you must collapse multiple matching rows in the source table into a single row beforehand. For example, you can aggregate multiple rows incrementing a column value in the source_table_name reference using a GROUP BY clause.

If the source is guaranteed to be a single row (that is, you specify an explicit value list or it is single table subquery with a UPI or USI constraint specified), then the ON clause predicate can have an equality condition between the primary index and a constant value, and the partitioning column set (if the target is a row-partitioned table) and a constant value.

The INSERT specification can either match or not match with the constant specified in the ON clause equality condition.

When the USING subquery guarantees a single-row source relation by specifying an equality condition on a unique index, you do not need to specify the partitioning column set in the subquery. See Example: With a Guaranteed Single-Row Source Relation, You Do Not Need To Specify the Partitioning Column Set.

Rules for Target Tables in a MERGE Statement

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#L n 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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 . 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.

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

Rules for MERGE WHEN MATCHED and WHEN NOT MATCHED

A MERGE request can specify at most one WHEN MATCHED clause and at most one WHEN NOT MATCHED clause.

When a MERGE request specifies a WHEN MATCHED and a WHEN NOT MATCHED clause, then the INSERT and UPDATE specifications of those clauses must apply to the same AMP.

The value specified for the primary index in the INSERT specification must match the primary index of the target table specified in the ON clause.

For a MERGE statement with a WHEN MATCHED clause, you must have the UPDATE privilege on every column that is being updated in the target table or the DELETE privilege on every column that is being deleted from the target table.

You must also have the SELECT privilege on the columns referenced in conditions and right-hand side of assignments for your MERGE request.

MERGE request UPDATE specifications have the same restrictions as an UPDATE request. See UPDATE.

The match_condition of a WHEN MATCHED clause must fully specify the primary index of the target table.

To use the WHEN NOT MATCHED clause, you must have the INSERT privilege on every column of the target table. You must also have the SELECT privilege on the columns referenced in conditions and right-hand side of assignments for your MERGE request.

MERGE request INSERT specifications have the same restrictions as an INSERT request with the exception that you cannot INSERT duplicate rows into a table even if it is defined as MULTISET and the request is made in an ANSI mode session. See INSERT/INSERT … SELECT

You cannot specify columns referring to a table that is neither the source table nor the target table in a WHEN MATCHED or WHEN NOT MATCHED clause.

You cannot specify target table columns as values in the INSERT specification for a WHEN NOT MATCHED THEN clause because rows must be inserted from a source table, and an INSERT merged row cannot exist as a hybrid of source and target table rows. The following request is not valid because its INSERT specification includes target table column z1.

     MERGE INTO t1
     USING (SELECT x2, y2, z3
            FROM t2, t3
            WHERE y2=10) AS s
       ON x1=y2 AND t4.x4=z2
     WHEN MATCHED THEN
       UPDATE SET z1=10
     WHEN NOT MATCHED THEN
       INSERT (x1, y1,  z1) VALUES (y2,  t1.z1, x2);

Rules for MERGE Statements with DELETE

The MERGE-INTO statement with the DELETE clause deletes the target table rows for which the condition evaluates to true. The source_table_name following the USING keyword specifies the target table for deletes.

WHEN MATCHED THEN can include either UPDATE or DELETE, but not both.

If you specify DELETE, you cannot specify INSERT.

If the DELETE clause is specified and there is a DELETE trigger defined on the target object, the MERGE-INTO statement can act as the triggering event for the DELETE trigger.

Rules for Using MERGE Requests With Embedded SQL Applications

You can embed MERGE requests in an embedded SQL application program, then submit them interactively or prepare and then execute them dynamically.

You can specify host variables for the WHERE clause of the USING subquery clause (but not in its select list), match_condition, update_expression, and insert_expression.

All host variables must be preceded by a COLON character.

Miscellaneous Rules for MERGE Requests

Rules for Using the DEFAULT Function With MERGE Requests

The following rules apply when using a DEFAULT function within a MERGE request:
  • The DEFAULT function takes a single argument that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the request is executed.

    The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.

  • You can specify the DEFAULT function as DEFAULT or DEFAULT (column_name). When a column name is not specified, the system derives the column based on context. If the column context cannot be derived, the request aborts and an error is returned to the requestor.
  • All the rules listed for the UPDATE statement also apply to the UPDATE in a MERGE statement. See Rules for Using the DEFAULT Function With Update.
  • All the rules listed for the INSERT statement also apply to the INSERT in a MERGE statement. See Inserting When Using a DEFAULT Function.
  • When the SQL Flagger is enabled, a DEFAULT function specified with a column name argument for inserts or updates is flagged as a Teradata extension.

For more information about the DEFAULT function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Rules for Using MERGE With Row-Partitioned Tables

The following rules apply to using the MERGE statement to insert rows into a row-partitioned primary index table or updating the columns of a partitioning expression.
  • The target table can be row partitioned. However, the target table cannot be column partitioned.
  • For MERGE requests that update the partitioning columns of a table, a partitioning expression must result in a value between 1 and the number of partitions defined for that level.
  • For MERGE requests that insert a row into a table, the partitioning expression for that row must result in a value between 1 and the number of partitions defined for that level.
  • If you specify a Period column as part of a partitioning expression, then you can only specify equality conditions on that Period column for a MERGE request. A MERGE request that specifies inequality conditions on a Period column included in a partitioning expression for the table returns an error.

    You can specify a Period column that is not defined as part of a partitioning expression for both equality and inequality conditions on that column for a MERGE request.

  • If you specify a function that references BEGIN or END Period values in a partitioning expression, an equality condition on that function is processed as a partitioning value matching condition.

    If you specify a function that references a BEGIN and an END Period in a partitioning expression, the system processes the equality condition on the BEGIN and END as a partitioning matching condition.

    Such a request must result in a single partition.

  • The system processes the conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED as equality conditions for the function that references the END Period only. See Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .
  • The INSERT clause must specify the same partitioning column values as the match condition.

    This rule also applies when the matching condition specifies a function that references a Period.

  • You cannot update the system-derived columns PARTITION and PARTITION#L1 through PARTITION#L62.
  • You cannot insert either a value or a null into any of the system-derived PARTITION columns.
  • Errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect at the time the error occurs.
Session Mode Work Unit Rolled Back in Response to Expression Evaluation Errors
ANSI Request that contains the error.
Teradata Transaction that contains the error.
Take care in designing your partitioning expressions to avoid expression errors.
  • For the merge operation to succeed, the session mode and collation at the time the table was created do not need to match the current session mode and collation. This is because the row partition in which a row is to be inserted or updated is determined by evaluating the partitioning expression on partitioning column values using the table’s session mode and collation.
  • Collation has the following implication for merging rows into a table defined with a character partitioning. If the collation for the table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, the system aborts any request that attempts to merge a row into the table and returns an error to the requestor.
  • If the partitioning expression for a table involves Unicode character expressions or literals, and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, the database aborts any attempts to insert rows into the table and returns an error to the requestor.

Rules for Using MERGE on Tables with Row-Partitioned Join Indexes

Following are the rules for using MERGE on tables with row-partitioned join indexes:
  • For MERGE requests that insert or update a row in a base table that causes an insert into a join index with row partitioning, the partitioning expression for that index row must result in a value between 1 and the number of partitions defined for that level.
  • For MERGE requests that insert or update a row in a base table that causes an update of an index row in a join index with row partitioning, the partitioning expression for that index row after the update must result in a value between 1 and the number of partitions defined for that level.
  • Merging a row into a base table does not always cause inserts or updates to a join index on that base table.

    For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index.

    The process for this activity is as follows:
    1. The database checks the WHERE clause condition for its truth value after the update to the row.
      IF the condition evaluates to … THEN the system …
      FALSE deletes the row from the sparse join index.
      TRUE retains the row in the sparse join index and proceeds to stage b.
    2. The database evaluates the new result of the partitioning expression for the updated row.
      Partitioning Expression Evaluation Result
      Null and no appropriate NO RANGE and UNKNOWN option has been specified. Request returns error and does not update either the base table or the sparse join index.
      Null and appropriate NO RANGE and UNKNOWN options have been specified. Stores the row in either the NO RANGE or UNKNOWN partition for the sparse join index, and continues processing requests.
      Value. Stores the row in the appropriate partition, which might be different from the partition in which it was previously stored, and continues processing requests.
  • Collation has the following implications for merging rows into a table defined with a character partitioning:

    If a noncompressed join index with a character partitioning under either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table and the definition for the collation has changed since the join index was created, a request that attempts to merge a row into the table returns an error, whether the merge would have resulted in rows being modified in the join index or not.

    If the partitioning expression for a noncompressed join index involves Unicode character expressions or literals, and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, an attempt to insert rows into the table returns an error.

Rules for Invoking a Scalar UDF From a MERGE Request

You can invoke a scalar UDF from the following clauses of a MERGE request.
  • SELECT

    You can invoke a scalar UDF from the USING SELECT clause of a MERGE request.

    You must use the alias to reference the result of a scalar UDF invocation that has an alias.

  • ON

    You can invoke a scalar UDF from the ON clause of a MERGE request as a secondary condition if it is invoked within an expression within the specified search condition or in the primary condition when it is bound to the primary index.

    The other rules that are applicable to the ON clause of a SELECT request are applicable to the ON clause of a MERGE request. See SELECT and Join Expressions.

  • UPDATE … SET

    You can invoke a scalar UDF from the right-hand side of the SET clause in an UPDATE request as long as the UDF always return a value expression.

  • INSERT … VALUES

    You can invoke a scalar UDF from the VALUES clause of the INSERT specification of a MERGE request.

    The rules for specifying a scalar UDF in the VALUES clause of an INSERT specification are as follows:
    • The arguments passed to a scalar UDF are restricted to the following categories:
      • Constants
      • Parameters that resolve to a constant
      • USING values
    • The scalar UDF must always return a value expression.

MERGE Insert Operations, MERGE Update Operations, and UDTs

For the rules about inserting into UDT columns, see Inserting into UDT Columns.

Rules for Using Scalar Subqueries With MERGE Requests

You cannot specify scalar subqueries in MERGE requests.

Rule for Using MERGE With Tables Protected by Row-Level Security Constraints

You can use MERGE requests to:
  • Update tables that have row-level security constraints (as long as the tables are defined with the same row-level security constraints).
  • Insert rows into target tables.

Rule for Queue Tables and MERGE

The following restrictions apply to specifying queue tables in a MERGE request:
  • You cannot specify a MERGE statement in a multistatement request that contains a SELECT AND CONSUME request for the same queue table.
  • You cannot specify a SELECT AND CONSUME request as the subquery in the USING clause of a MERGE request.

MERGE as a Triggering Action

If subquery returns no rows, then no triggers are fired.

Triggers invoke the following behavior when fired as the result of a MERGE request:

Type of Action for which Trigger is Defined Clause that Fires Trigger match_condition Result
UPDATE WHEN MATCHED THEN Met.
DELETE WHEN MATCHED THEN Met.
INSERT WHEN NOT MATCHED THEN Not met.

MERGE as a Triggered Action

MERGE is not supported as a triggered action.

MERGE Support for Load Isolated Tables

A nonconcurrent load isolated merge delete operation on a load isolated table physically deletes the matched rows. A nonconcurrent isolated merge update operation on a load isolated table updates the matched rows in-place.

A concurrent load isolated merge delete operation on a load isolated table logically deletes the matched rows. A concurrent load isolated merge update operation on a load isolated table logically deletes the matched rows and inserts the rows with the modified values. The rows are marked as deleted and the space is not reclaimed until you issue an ALTER TABLE statement with the RELEASE DELETED ROWS option. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.