MERGE

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.

IF the source and target rows …	THEN the merge operation …
satisfy the matching condition	updates based on the WHEN MATCHED THEN UPDATE clause.
satisfy the matching condition	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.

Syntax

where:

Syntax element …	Specifies …
target_table	the base data table, global temporary table, volatile table, or queue table to: update rows in or delete rows from. insert rows into. The target table must have a primary index.
correlation_name	an optional alias for target_table.
USING	an introduction to the table expression that defines the source table rows for the merge operation.
using_expression	an expression defining the columns of the table expression that specifies the source table rows for the merge as a list of using_expression values. The table expression representing the source table rows is composed of the comma-separated list of using_expression values. It is not a single value expression. using_expression can reference any of the following: Constants Built-in functions. For details, see “Built-In Functions” in SQL Functions, Operators, Expressions, and Predicates. USING request modifier variables Macro parameters Stored procedure parameters Host variables String literals The target table for the MERGE operation. For example, you can use the target table as the source table, but in that case, the target table becomes another instance table that contains the row data as it was prior to the update to the target table by the MERGE operation (see “Example 18: Using the Target Table as the Source Table” on page 479). using_expression cannot reference columns in any other base table.
subquery	a subquery table expression that specifies the source table for the merge. You can specify host variables in the WHERE clause, but not in the select list, of subquery. All host variables must be preceded by a COLON character. If subquery returns no rows, the source table is empty, no triggers are fired (see “MERGE as a Triggering Action” on page 466), and the merge operation performs neither update nor insert operations on target_table. subquery can reference any of the following: Rows in the queried table Constants USING request modifier variables subquery cannot specify an ORDER BY clause. The WHERE clause for subquery must include an equality condition on the UPI or USI of the queried table to ensure a singleton select.
source_table_name	the name of the source table to be merged into target_table.
column_name	optional names to be used for the source row columns defined by the corresponding using_expression or subquery select list expression or by source_table_name. If you do not specify any column names, MERGE uses the column names specified in the using_expression or subquery of the USING clause. Source row columns, qualified by source_table_name, can be referenced in match_condition or in an update_expression or insert_expression.
AS correlation_name	an optional correlation name for the source table specified by using_expression, subquery, or source_table_name.
match_condition	a conditional expression that determines whether the source row matches a given row in the target table. If the condition is met for any target rows and a WHEN MATCHED clause is specified, then the matching target rows are updated or deleted. match_condition must specify an equality constraint on the primary index of target_table to ensure that the candidate target row set can be hash‑accessed on a single AMP. The specified primary index value must match the primary index value implied by the column values specified in the WHEN NOT MATCHED clause. If the primary index value is the result of a using_expression, the expression cannot reference any column in target_table. Additionally, match_condition cannot specify subqueries or references to columns that do not belong to either the source table or to target_table. If target_table is a PPI table, the values of the partitioning columns must also be specified in match_condition, and the WHEN NOT MATCHED clause must specify the same partitioning column values as match_condition. Host variables are permitted in match_condition. All host variables must be preceded by a COLON character. match_condition cannot reference a table that is neither the target table nor the source table.
WHEN MATCHED THEN	an introduction to the operation to be performed on matching rows. You can specify WHEN MATCHED THEN and WHEN NOT MATCHED THEN clauses in any order.
UPDATE SET UPD SET	an update set clause operation to be performed for matching rows.
update_column = update_expression	an equality condition on a target table column (specified by update_column) that defines how the specified field is to be updated. update_expression produces a new value to be placed into the field to be updated. update_expression can include source table column references, target table column references, a constant, a null expressed by the reserved word NULL, a DEFAULT function, host variables, or an arithmetic expression for calculating the new value. All host variables must be preceded by a COLON character. You cannot specify a derived period column name.
DELETE	that matching rows are deleted. Note: DELETE cannot be combined with INSERT or UPDATE in a MERGE statement.
WHEN NOT MATCHED THEN	an introduction to the operation to be performed on nonmatching rows. You can specify WHEN MATCHED and WHEN NOT MATCHED clauses in any order. You can also specify a WHEN NOT MATCHED clause without also specifying a WHEN MATCHED clause.
INSERT INS	an introduction to a value list to be inserted for nonmatching rows. Because an inserted row might be a duplicate of an existing row in target_table, its acceptance depends on whether target_table is defined to be SET or MULTISET.
VALUES	an optional keyword for the value list.
insert_column	a column name into which a corresponding value in the value list is to be inserted for nonmatching rows.
insert_expression	a value to be inserted into a corresponding insert_column for nonmatching rows. Host variables are permitted in insert_expression. All host variables must be preceded by a COLON character.
Begin LOGGING ERRORS option
LOGGING ERRORS LOGGING ALL ERRORS	to log all data errors, reference index errors, and USI errors. If you do not specify the LOGGING ERRORS option, the system does no error handling. If an error occurs, the following session‑mode behavior occurs: The optional keyword ALL is the default. If the current session mode is ANSI, then the erring request aborts and rolls back. If the current session mode is Teradata, then the erring transaction aborts and rolls back.
Begin LOGGING ERRORS LIMIT option If you do not specify a value for the LIMIT option, the system defaults to a 10 error limit.
WITH NO LIMIT	that there is no limit to the number of errors that can be logged in the error table associated with the target data table for this MERGE operation. Note that this does not mean that there is no limit on the number of errors the system can log for a MERGE request; instead, it means that errors will continue to be logged until the system-determined limit of 16,000,000 errors have been logged. See “CREATE ERROR TABLE” in SQL Data Definition Language.
WITH LIMIT OF error_limit	that the limit on the number of errors that can be logged in the error table associated with the target data table for this MERGE operation is error_limit. If this limit is exceeded, the system aborts the request in Teradata session mode or the transaction in ANSI session mode, and rolls back all changes made to the target table, but does not roll back the logged error table rows. The value you specify for error_limit can be anything in the range from 1 through 16,000,000, inclusive. The default value for error_limit is 10.
End LOGGING ERRORS LIMIT option
End LOGGING ERRORS option

ANSI Compliance

MERGE is ANSI SQL:2011-compliant.

Note that in the ANSI definition, this statement is named MERGE INTO, while in the Teradata definition, INTO is an optional keyword.

Required Privileges

The privileges required to perform MERGE depend on the merge matching clause of the request you submit.

IF you specify this type of merge matching clause …	THEN you must have this privilege …
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	SELECT on any source table specified in a USING subquery. all the update and insert privileges required for the WHEN MATCHED and WHEN NOT MATCHED clauses. 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.

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 PPI 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

Locks and Concurrency

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

IF the transaction isolation level is …	AND the DBS Control AccessLockForUncomRead flag is set …	THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is …	AND the default locking severity for SELECT operations embedded within a MERGE request is …
SERIALIZABLE	FALSE	READ	READ
SERIALIZABLE	TRUE		READ
READ UNCOMMITTED	FALSE		READ
READ UNCOMMITTED	TRUE		ACCESS

For More Information

“SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language

SQL Request and Transaction Processing

Utilities

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.

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.

Case 1

The query plan includes a table‑level WRITE lock on target table t1 in steps 1 and 2.

     EXPLAIN MERGE INTO t1

     USING (SELECT a2, b2, c2

            FROM t2

            WHERE a2 = 1) AS source (a2, b2, c2)

       ON a1 = a2

     WHEN MATCHED THEN

       UPDATE SET b1 = b2

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

     Explanation

     ---------------------------------------------------------------------------

       1) First, we lock a distinct OB."pseudo table" for write on a RowHash

          to prevent global deadlock for OB.t1.

       2) Next, we lock OB.t1 for write.

       3) We do a single-AMP RETRIEVE step from OB.t2 by way of the unique

          primary index "OB.t2.a2 = 1" with no residual conditions into

          Spool 1 (used to materialize view, derived table or table function

          source) (one-amp), which is built locally on that AMP.  Then we do

          a SORT to order Spool 1 by the hash code of (OB.t2.a2).  The size

          of Spool 1 is estimated with low confidence to be 1 row (33 bytes).

          The estimated time for this step is 0.02 seconds.

       4) We do an all-AMPs merge with matched updates and unmatched inserts

          into OB.t1 from Spool 1 (Last Use) with a condition of ("OB.t1.a1

          = A2").  The number of rows merged is estimated with low

          confidence to be 1 row.

       5) Finally, we send out an END TRANSACTION step to all AMPs involved

          in processing the request.

       -> No rows are returned to the user as the result of statement 1.

Case 2

Locking considerations become critical when you submit MERGE requests in an array‑processing environment because those requests can cause transaction deadlocks if they are not coded correctly. For details, see Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems. See also SQL Request and Transaction Processing.

This case is an example of how improper coding can cause deadlocks. In this case, the same request is repeated once each time through two different sessions as specified by the BTEQ command .REPEAT 2 PACK 100. Because the sessions are running under ANSI transaction semantics, the lock on t1 cannot be released until the COMMIT request is processed successfully.

Suppose there are two sessions, numbers 1025 and 1026. Assume further that session 1026 executes first. Session 1026 places a table‑level WRITE lock on target table t1 and completes the execution of the MERGE request. However, session 1026 cannot release the lock on target table t1 until session 1025 completes because its transaction is not committed until both sessions have completed.

Session 1025 cannot complete its transaction because session 1026 has a table‑level WRITE lock on t1. This is a classic case of deadlock where both sessions are waiting on one other for the lock to be released, causing both requests to hang.

     .SET SESSION TRANSACTION ANSI

     .SET SESSIONS 2

     .LOGON nbmps05/ob,ob

     .IMPORT INDICDATA file = ./recind.data

     .REPEAT 2

     USING (c3 INTEGER)

     MERGE INTO t1

     USING (SELECT a2, b2, c2

            FROM t2

            WHERE a2 = 1) AS source (a2, b2, c2)

       ON a1 = a2 AND c1 =:c3

     WHEN MATCHED THEN

       UPDATE SET b1 = b2

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

     .REPEAT 20

     COMMIT;

There are two ways to avoid the deadlock that results from this case:

Redesign the application.

Run the existing application in Teradata session mode to avoid the problem with the transaction‑terminating COMMIT request.

About the MERGE Statement

The MERGE statement combines the UPDATE and INSERT statements into a single statement with two conditional test clauses:

WHEN MATCHED, UPDATE.

WHEN NOT MATCHED, INSERT.

You can also use the MERGE statement to delete rows by specifying: WHEN MATCHED, DELETE.

The following table explains the meaning of these conditional clauses:

IF this clause evaluates to TRUE …

THEN MERGE …

WHEN MATCHED

updates a matching target table row with the set of values taken from the current source row.

deletes a matching target table row.

WHEN NOT MATCHED

inserts the current source row into the target table.

A MERGE statement can specify one WHEN MATCHED clause and one WHEN NOT MATCHED clause, in either order. You need not specify both. However, you must specify at least one of these clauses.

The Merge with Matched Updates and Unmatched Inserts AMP step performs inserts and updates in a single step. A merge with matched updates and unmatched inserts step can perform any of the following operations:

INSERT only

UPDATE only

INSERT and UPDATE

A merge with matched deletes performs a DELETE only.

The merge with matched updates and unmatched inserts step assumes that the source table is always distributed on the join column of the source table, which is specified in the ON clause as an equality constraint with the primary index of the target table and sorted on the RowKey.

The step does a RowKey-based Merge Join internally, identifying source rows that qualify for updating target rows and source rows that qualify for inserts, after which it performs those updates and inserts.

This step is very similar to the APPLY phase of MultiLoad because it guarantees that the target table data block is read and written only once during the MERGE operation.

The order of evaluating whether a source row should be inserted into the target table or whether a matching target table row should be updated with the value set taken from the source varies accordingly. Note that the result depends on the order in which you specify the two clauses.

MERGE Statement Processing

This section describes the general actions performed by a MERGE statement:

The Venn diagram below divides the source table rows into two disjunct sets: set A and set B. Set A is the set of matching rows and set B is the set of nonmatching rows.

This description uses the following terms:

The target_table variable specifies the target table being modified.

The correlation_name variable is an optional alias for the target table.

The source_table_name variable following the USING keyword specifies the source table whose rows act as the source for update or delete and insert operations.

The match_condition variable following the ON keyword divides the source table into a set of rows that match rows in the target table and a set of rows that do not match rows in the target table.

The set of matching rows, A, defines the update or delete source, or staging, table.

The set of nonmatching rows, B, defines the insert source, or staging, table.

Either set can be empty, but both cannot be empty for a MERGE operation.

The system uses the set of matching rows as the update or delete source table for the update operation, as specified by the update_column=update_expression variable.

The system uses the set of nonmatching rows as the insert source table for the insert operation, as specified by the insert_expression variable.

MERGE Update and Insert Order of Application

The order of application of MERGE update and insert operations depends on the order in which you specify their respective WHEN MATCHED and WHEN NOT MATCHED clauses when you code your MERGE request. For details, see “Exceptions to Full ANSI Compliance” on page 432.

MERGE With Triggers

When a MERGE statement with a MERGE WHEN MATCHED clause executes, the system activates triggers defined on UPDATE or DELETE operations.

When a MERGE statement with a MERGE WHEN NOT MATCHED clause executes, the system activates triggers defined on INSERT operations.

The order of activation of UPDATE and INSERT triggers is the same as the order of the MERGE WHEN MATCHED and MERGE WHEN NOT MATCHED clauses in the MERGE request.

The following orders of operations apply to MERGE operations on source tables that have multiple rows. Assume for all cases that both updates to existing rows in the target table and inserts of new rows into the target table occur without incident.

MERGE Order of Operations

The order of operations depends on whether you specify the MERGE WHEN MATCHED (update) clause first or the MERGE WHEN NOT MATCHED (insert) clause first as follows.

MERGE WHEN MATCHED (Update) First

The sequence of actions when the MERGE statement executes are as follows:

1 All BEFORE triggers associated with UPDATE actions are applied.

2 All BEFORE triggers associated with INSERT actions are applied.

3 The UPDATE operations specified by the MERGE update specification and the INSERT operations specified by the MERGE insert specification are applied.

4 The system checks any specified constraints, which might result in referential actions being executed.

5 All AFTER triggers associated with UPDATE actions are applied.

6 All AFTER triggers associated with INSERT actions are applied.

MERGE WHEN NOT MATCHED (Insert) First

The sequence of actions when the MERGE statement executes are as follows.

1 All BEFORE triggers associated with INSERT actions are applied.

2 All BEFORE triggers associated with UPDATE actions are applied.

3 The INSERT operations specified by the MERGE insert specification and the UPDATE operations specified by the MERGE update specification are applied.

4 The system checks any specified constraints, which might result in referential actions being executed.

5 All AFTER triggers associated with INSERT actions are applied.

6 All AFTER triggers associated with UPDATE actions are applied.

MERGE With Duplicate Rows

MERGE processes duplicate UPDATE rows in the same way that the UPDATE statement does, and it processes duplicate INSERT rows in the same way that an INSERT … SELECT request does, as indicated by the following bullets.

MERGE INSERT Duplicate Rows

When MERGE detects duplicate rows during an INSERT operation, then Teradata Database takes different actions on the request depending on several factors.

If no error logging is specified for the request AND the target table is a SET table AND the session is in ANSI session mode, then the request aborts and rolls back.

If error logging is specified for the request AND the target table is a SET table AND the session is in ANSI session mode, then 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.

If the target table is a SET table AND the session is in Teradata session mode, then any duplicate rows are silently ignored.

The INSERT source relation for a MERGE statement can contain duplicate rows.

Like the case for an INSERT … SELECT statement, MERGE silently ignores duplicate row INSERT attempts into a SET table in Teradata session mode.

When the system inserts rows into the target table, the insertion of duplicate rows is governed by the normal constraint check rules enforced by the session mode types:

IN this session mode …

The system handles duplicate row insert attempts by …

ANSI

not inserting them into the target table.

It logs them as errors in the appropriate error table.

The system inserts no rows into the target table under these circumstances.

Teradata

inserting the first row of the duplicate set into the target table and rejecting all the remaining rows from that set without logging them as errors.

The system inserts one row into the target table under these circumstances.

Unlike an INSERT … SELECT statement, MERGE does not silently ignore duplicate row insert attempts into a SET table in Teradata session mode.

MERGE UPDATE Duplicate Rows

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.

Rules and Limitations for MERGE

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

The following set of rules applies to 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.

PPI Tables Primary Condition Definition

For PPI 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 PPI 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 (and partitioning column value if the target table has a PPI) value 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 a PPI

For a target table with a PPI, 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 file 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 SQL Functions, Operators, Expressions, and Predicates.

     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 SQL Data Definition Language. 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 a PPI), and the expression specifies only source table columns.

There are three 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 a PPI).

See “Example 12” on page 474 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” on page 116), which include not 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 PPI 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 17: With a Guaranteed Single‑Row Source Relation, You Do Not Need To Specify the Partitioning Column Set” on page 478).

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 PPI 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 447.

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” on page 490.

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 (see “INSERT/INSERT … SELECT” on page 370) 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.

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

You cannot specify INSERT DEFAULT VALUES in a MERGE request.

MERGE supports UDTs.

See “MERGE Insert Operations and UDTs” on page 465 and “MERGE Update Operations and UDTs” on page 465.

MERGE can be specified as a triggering action. See “MERGE as a Triggering Action” on page 466 and “CREATE TRIGGER” in SQL Data Definition Language.

MERGE cannot be specified as a triggered action. See “MERGE as a Triggered Action” on page 466 and “CREATE TRIGGER” in SQL Data Definition Language.

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” on page 504.

All the rules listed for the INSERT statement also apply to the INSERT in a MERGE statement. See “Rules for Inserting When Using a DEFAULT Function” on page 384.

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 SQL Functions, Operators, Expressions, and Predicates.

Large Objects and MERGE

The behavior of truncated LOB inserts and updates differs in ANSI and Teradata session modes. The following table explains the differences in truncation behavior.

Session mode …

Occurs when non‑pad bytes are truncated on insertion …

ANSI

an exception condition is raised.

The INSERT or UPDATE fails.

Teradata

no exception condition is raised.

The INSERT or UPDATE succeeds: the truncated LOB is stored.

Rules for MERGE with Nonpartitioned NoPI and Column‑Partitioned Tables

The following rule applies to using MERGE requests with nonpartitioned NoPI and column‑partitioned tables.

You cannot use MERGE requests to move rows into nonpartitioned NoPI or column‑partitioned target tables. This is true regardless of the setting of the DBS Control flag PrimaryIndexDefault.

For More Information

For more information about nonpartitioned NoPI and column-partitioned tables, see Database Design.

For more information about the PrimaryIndexDefault flag, see:

“CREATE TABLE” in SQL Data Definition Language

Utilities: Volume 1 (A-K)

Database Design

Rules for Using MERGE With PPI Tables

The following rules apply to using the MERGE statement to insert rows into a partitioned primary index table or updating the columns of a PPI table partitioning expression.

For MERGE requests that update the partitioning columns of a table with row partitioning, the 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 with row partitioning, the partitioning expression for that 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 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.

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. If you submit a MERGE request that specifies inequality conditions on a Period column that is specified in a partitioning expression for the table, then Teradata Database aborts the request and returns an error.

On the other hand, 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 BEGIN or END Period bound function in the partitioning expression for a PPI table, then an equality condition on that function is treated as a PPI bound matching condition.

If you specify both a BEGIN and an END Period bound function in the partitioning expression, then Teradata Database treats an equality condition on both bounds as a PPI bound matching condition.

Such a request must result in a single partition.

Teradata Database treats the conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED as equality conditions for the END Period bound function only. See Temporal Table Support for more information.

If the target table of a MERGE request is a PPI table, then the INSERT clause must specify the same partitioning column values as the match condition.

This rule also applies when the matching condition specifies Period bound functions.

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:

a Teradata 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.

b Teradata Database evaluates the new result of the partitioning expression for the updated row.

IF the partitioning expression …	THEN Teradata Database …
evaluates to null and no appropriate NO RANGE and UNKNOWN option has been specified	aborts the request, does not update either the base table or the sparse join index, and returns an error message to the requestor.
evaluates to null and appropriate NO RANGE and UNKNOWN options have been specified	handles the row by storing it in either the NO RANGE or UNKNOWN partition for the sparse join index, and continues processing requests.
evaluates to a 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.

You cannot update the system‑derived columns PARTITION and PARTITION#L1 through PARTITION#L15.

You cannot insert either a value or a null into any of the system‑derived PARTITION columns.

Expression evaluation 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.

In this session mode …	Expression evaluation errors roll back this work unit …
ANSI	request that contains the aborted request.
Teradata	transaction that contains the aborted request.

When you design your partitioning expressions, you should construct them in such a way that expression errors either cannot, or are very unlikely to, occur.

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 implications for merging rows into tables defined with a character partitioning:

If the collation for a PPI table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, Teradata Database aborts any request that attempts to merge a row into the table and returns an error to the requestor.

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, Teradata Database aborts any request that attempts to merge a row into the table and returns an error to the requestor whether the merge would have resulted in rows being modified in the join index or not.

If the partitioning expression for a table or 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, Teradata Database aborts any attempts to insert rows into the table and returns an error to the requestor.

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.

The rules for this invocation are the same as those for a standalone SELECT request (see “Invoking a Scalar UDF From a SELECT Statement” on page 37.

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” on page 12 and “Chapter 2 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.

Logging MERGE Errors In an Error Table

The assumption is that in the normal case, a MERGE request with error logging completes without any USI or RI errors. Exceptions to normal completion of a MERGE request are handled as follows:

Not all types of errors are logged when you specify the LOGGING ERRORS option for a MERGE request.

All local, or data errors, are logged.

These are errors that occur during row merge step processing, such as CHECK constraint, duplicate row, and UPI violation errors.

Errors that occur before the row merge step, such as data conversion errors detected in the RET AMP step before the MRG or MRM AMP steps, are not.

When the system encounters USI or RI errors (or both) in the MERGE operation, the following events occur in sequence:

a The transaction or request runs to completion

b The system writes all erring rows into the error table

c The system aborts the transaction or request

d The system rolls back the transaction or request

Note that the system does not invalidate indexes, nor does it roll error table rows back, enabling you to determine which rows in the MERGE set are problematic and to determine how to correct them.

If the number of errors in the request is large, running it to completion plus rolling back all the INSERT and UPDATE operations can exert an impact on performance. To minimize the potential significance of this problem, you should always consider specifying a WITH LIMIT OF error_limit clause.

The following rules and guidelines apply to logging errors in an error table for MERGE loads:

Before you can log errors for MERGE loads, you must create an error table (see “CREATE ERROR TABLE” in SQL Data Definition Language) for the base data table into which you intend to do a MERGE load.

If error logging is not enabled and you submit a MERGE load operation with the LOGGING ERRORS option specified, the system aborts the request and returns an error message to the requestor.

The LOGGING ERRORS option is valid in both ANSI and Teradata session modes.

The LOGGING ERRORS option is not valid in a multistatement request.

Two general categories of errors can be logged when you specify a LOGGING ERRORS option:

Local errors

Local errors are defined as errors that occur on the same AMP that inserts the data row. The following types of errors are classified as local errors:

Duplicate row errors, which occur only in ANSI session mode.

The system silently ignores duplicate row errors that occur from a MERGE into a SET table in Teradata session mode.

Duplicate rows can also arise from the following MERGE insert situations:

• The source table has duplicate rows.

• An insert is not well-behaved, meaning that the insert is made on a different AMP than the failed update.

Duplicate primary key errors

CHECK constraint violations

Attempts to update the same target table row with multiple source table rows

Nonlocal errors

Nonlocal errors are defined as errors that occur on an AMP that does not own the data row. The following types of errors are classified as nonlocal errors:

Referential integrity violations

USI violations

An exception to this is the case where a USI violation is local because the USI is on the same set of columns as the primary index. The system treats such an error as a nonlocal error, even though it is local in the strict definition of a local error.

The system response depends on the type of error:

IF this type of error occurs …	THEN the system records it in the error table, rejects the error‑causing rows from the target table, and …
local	completes the request or transaction successfully.
nonlocal	lets the request or transaction run to completion in order to record all the error causing rows in the MERGE load, then aborts the request or transaction and rolls back its inserts and updates.
both local and nonlocal

Teradata Database rejects data rows that cause local errors from the target table. However, the system does not reject data rows that cause nonlocal errors from the target table, but instead inserts them into the target table.

Teradata Database does not handle batch referential integrity violations for MERGE error logging. Because batch referential integrity checks are all-or-nothing operations, a batch referential integrity violation causes the system to respond in the following session mode‑specific ways:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

Teradata Database does not handle error conditions that do not allow useful recovery information to be logged in the error table. Such errors typically occur during intermediate processing of input data before it are built into a row format that corresponds to the target table.

Teradata Database detects this type of error before the start of data row inserts and updates. The following are examples of these types of error:

UDT, UDF, and table function errors

Version change errors

Nonexistent table errors

Down AMP request against nonfallback table errors

Data conversion errors

Note that the system handles conversion errors that occur during data row inserts as local data errors.

The way Teradata Database handles these errors depends on the current session mode, as explained by the following table:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.

Teradata Database inserts a marker row into the error table at the end of a successfully completed MERGE request with logged errors.

Marker rows have a value of 0 in the ETC_ErrorCode column of the error table, and their ETC_ErrSeq column stores the total number of errors logged. All other columns in a marker row except for ETC_DBQL_QID and ETC_TimeStamp are set to null.

If no marker row is recorded, the request or transaction was aborted and rolled back because of one or more of the following reasons:

The specified error limit was reached.

Teradata Database detected an error that it cannot handle.

Teradata Database detected a nonlocal (RI or USI) violation.

The system preserves the error rows that belong to the aborted request or transaction.

In addition to the previously listed errors, Teradata Database does not handle the following types of errors. However, it preserves the logged error rows for any of the errors listed.

Out of permanent space or our of spool space errors

Duplicate row errors in Teradata session mode, because the system ignores such errors in Teradata session mode

Trigger errors

Join index maintenance errors

Identity column errors

Implicit USI violations

When you create a table with a primary key that is not also the primary index, Teradata Database implicitly defines a USI on that primary key.

Teradata Database cannot invalidate a violated implicit USI on a primary key because it does not allow such a USI to be dropped and then recreated later.

Teradata Database handles the errors in the following session mode‑specific ways:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

The LOGGING ERRORS option is applicable to MERGE load requests whose target tables are permanent data tables only.

Other kinds of target tables, such as volatile and global temporary tables, are not supported.

The system returns a warning message to the requestor if it logs an error.

You cannot log errors for MERGE requests that specify unreasonable update or insert operations.

In this case, the request or transaction containing the erring MERGE request behaves as follows when the system detects the unreasonable INSERT specification:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

You can log all errors or not log errors. You cannot specify the types of errors to log. The WITH LIMIT OF error_limit option enables you to terminate error logging when the number of errors logged matches the number you specify in the optional WITH LIMIT OF error_limit clause.

If you do not specify a LOGGING ERRORS option, and an error table is defined for the target data table of the MERGE request, the system does no error handling for MERGE operations against that data table.

In this case, the request or transaction containing the erring MERGE request behaves as follows when an error occurs:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

If you specify neither the WITH NO LIMIT option, nor the WITH LIMIT OF error_limit option, the system defaults to an error limit of 10.

Teradata Database logs errors up to the limit of 10, and then the request of transaction containing the MERGE request behaves as follows when the eleventh error occurs:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.

WITH NO LIMIT

Teradata Database places no limit on the number of error rows that can accumulate in the error table associated with the target data table for the MERGE operation.

WITH LIMIT OF error_limit

Teradata Database logs errors up to the limit of error_limit, and then the request or transaction containing the MERGE request behaves as follows when the error_limit + 1 error occurs:

IF this session mode is in effect …	THEN the erring …
ANSI	request aborts and rolls back.
Teradata	transaction aborts and rolls back.

Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.

The activity count returned for a MERGE … LOGGING ERRORS request is the same as that returned for a MERGE operation without a LOGGING ERRORS option, a count of the total number of rows in the target table that were:

updated

inserted

deleted

Note: DELETE cannot be used with INSERT or UPDATE. INSERT and UPDATE can be used individually or together.

The possible activity types returned to the application are listed in the following table:

Activity Type Name	Activity Type Number	Description
PCLMRGMIXEDSTMT	127	A mix of updates and inserts.
PCLMRGUPDSTMT	128	All updates, no inserts or deletes.
PCLMRGINSSTMT	129	All inserts, no updates or deletes.
PCLMRGDELSTMT	206	All deletes, no updates or inserts.

A dynamic MERGE request returns the following activity types:

This activity type …	Is returned when this clause is specified for the MERGE request …
PCLMRGMIXEDSTMT	WHEN MATCHED and WHEN NOT MATCHED. If the MERGE request only updates rows of the target table, Teradata Database may internally convert the activity type to PCLMRGUPDSTMT. If the MERGE request only inserts rows into the target table, Teradata Database may internally convert the activity type to PCLMRGINSSTMT.
PCLMRGUPDSTMT	WHEN MATCHED THEN UPDATE only.
PCLMRGINSSTMT	WHEN NOT MATCHED THEN INSERT only.
PCLMRGDELSTMT	WHEN MATCHED THEN DELETE only.

Following is an example of a MERGE statement and resulting output:

MERGE INTO t1 target

   USING (SEL a, b, c FROM src WHERE a <= 100) AS source (a, b, c)

   ON target.a1 = source.a

      WHEN MATCHED UPDATE SET b1=b1+100

      WHEN NOT MATCHED THEN INSERT (a,b,c);

 *** Merge completed. 80 rows affected.

   30 rows inserted, 50 rows updated, no rows deleted.

See Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems for details about these activity types.

LOGGING ERRORS does not support LOB data. LOBs in the source table are not copied to the error table. They are represented in the error table by nulls.

An index violation error does not cause the associated index to be invalidated.

For referential integrity validation errors, you can use the IndexId value with the RI_Child_TablesVX view (see Data Dictionary for information about the RI_Child_TablesVX view) to identify the violated Reference index (see Database Design for information about Reference indexes). You can determine whether an index error is a USI or referential integrity error by the code stored in the ETC_IdxErrType error column.

IF the value of ETC_IdxErrType is …	THEN the error is a …
R	foreign key insert violation.
r	parent key delete violation.
U	USI validation error.

MERGE Insert Operations and UDTs

See “Inserting Into Distinct UDT Columns” on page 375 and “Inserting Into Structured UDT Columns” on page 376 for the rules about inserting into UDT columns.

MERGE Update Operations and UDTs

See “Updating Distinct UDT Columns” on page 500 and “Updating Structured UDT Columns” on page 501 for the rules about 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:

IF a trigger is defined on this type of action …	THEN it is fired when this clause is specified …	AND match_condition is …
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.

Example

This example uses dynamically supplied values for an employee table row to update the table if the data matches an existing employee or insert the new row into the table if the data does not match an existing employee. Column empno is the unique primary index for the employee table.

     USING (empno  INTEGER,

            name   VARCHAR(50),

            salary INTEGER)

     MERGE INTO employee AS t

     USING VALUES (:empno, :name, :salary) AS s(empno, name, salary)

       ON t.empno=s.empno

     WHEN MATCHED THEN UPDATE

       SET salary=s.salary

     WHEN NOT MATCHED THEN INSERT (empno, name, salary)

       VALUES (s.empno, s.name, s.salary);

This example could also be coded as the following upsert form of the UPDATE statement. See “UPDATE (Upsert Form)” on page 515.

     USING (empno  INTEGER,

            name   VARCHAR(50),

            salary INTEGER)

     UPDATE  employee

       SET salary=:salary

       WHERE empno=:empno

       ELSE INSERT INTO employee

         (empno, name, salary) VALUES ( :empno, :name, :salary);

Example

This example generalizes “Example 1” by using a subquery for source_table_reference rather than an explicit value list.

     USING (empno  INTEGER,

            salary INTEGER)

     MERGE INTO employee AS t

     USING (SELECT :empno, :salary, name

            FROM names

            WHERE empno=:empno) AS s(empno, salary, name)

       ON t.empno=s.empno

     WHEN MATCHED THEN UPDATE

      SET salary=s.salary, name = s.name

     WHEN NOT MATCHED THEN INSERT (empno, name, salary)

      VALUES (s.empno, s.name, s.salary);

Example : Using the DEFAULT Function With MERGE

The following examples show the correct use of the DEFAULT function within the MERGE statement.

     MERGE INTO emp

     USING VALUES (100, 'cc', 200, 3333) AS emp1 (empnum, name,

                                                  deptno, sal)

       ON emp1.empnum=emp.s_no

     WHEN MATCHED THEN

       UPDATE SET sal=DEFAULT

     WHEN NOT MATCHED THEN

       INSERT VALUES (emp1.empnum, emp1.name, emp1.deptno, emp1.sal);

     MERGE INTO emp

     USING VALUES (100, 'cc', 200, 3333) AS emp1 (empnum, name,

                                                  deptno, sal)

       ON emp1.empnum=emp.s_no

     WHEN MATCHED THEN

       UPDATE SET sal=DEFAULT(emp.sal)

     WHEN NOT MATCHED THEN

       INSERT VALUES (emp1.empnum, emp1.name, emp1.deptno, emp1.sal);

Example : Logging MERGE Errors

The following MERGE request logs all error types, including data errors, referential integrity errors, and USI errors, with an error limit of 100 errors.

     MERGE INTO tgttbl AS t1

     USING  (SELECT c1,c2,c3

             FROM srctbl) AS t2

       ON t1.c1=t2.c1

     WHEN MATCHED THEN

       UPDATE SET t1.c2=t2.c2 + t1.c2,  t1.c3=t2.c3

     WHEN NOT MATCHED THEN

       INSERT INTO t1 VALUES (t2.c1, t2.c2, t2.c3)

     LOGGING ERRORS WITH LIMIT OF 100;

Example

The biggest benefit of the MERGE statement is its ability to do both update and insert operations within a single SQL request. MERGE can also perform index and referential integrity maintenance in a single pass, unlike the case where update and insert operations must be executed separately.

For example, suppose you create the following tables and then use a MERGE request to update and insert rows from source table t2 into target table t1:

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER,

       c1 INTEGER);

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER,

       c2 INTEGER);

     EXPLAIN MERGE INTO t1

             USING t2

             ON a1=a2

               WHEN MATCHED THEN

                 UPDATE

                 SET b1=b2

               WHEN NOT MATCHED THEN

                 INSERT (a2, b2, c2);

      *** Help information returned. 13 rows.

       *** Total elapsed time was 1 second.

     Explanation

     ---------------------------------------------------------------------------

     1) First, we lock a distinct OB."pseudo table" for read on a RowHash

        to prevent global deadlock for OB.t2.

     2) Next, we lock a distinct OB."pseudo table" for write on a RowHash

     to prevent global deadlock for OB.t1.

     3) We lock OB.t2 for read, and we lock OB.t1 for write.

     4) We do an all-AMPs merge with matched updates and unmatched inserts

     into OB.t1 from OB.t2 with a condition of ("OB.t1.a1 = OB.t2.a2").

     The number of rows merged is estimated with low confidence to be 2

     rows.

     5) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

     -> No rows are returned to the user as the result of statement 1.

This MERGE request can also be coded as the following semantically equivalent multistatement UPDATE INSERT request:

     EXPLAIN UPDATE t1

             FROM t2

             SET b1=b2

             WHERE a1=a2

            ;INSERT INTO t1

             SELECT a2, b2, c2

             FROM t2, t1

             WHERE NOT (a1=a2);

      *** Help information returned. 32 rows.

       *** Total elapsed time was 1 second.

You can see by comparing the two EXPLAIN reports that the MERGE request would outperform the semantically equivalent UPDATE and INSERT multistatement request.

     Explanation

     ---------------------------------------------------------------------------

     1) First, we lock a distinct OB."pseudo table" for read on a RowHash

        to prevent global deadlock for OB.t2.

     2) Next, we lock a distinct OB."pseudo table" for write on a RowHash

        to prevent global deadlock for OB.t1.

     3) We lock OB.t2 for read, and we lock OB.t1 for write.

     4) We execute the following steps in parallel.

          1) We do a MERGE Update to OB.t1 from OB.t2 by way of a RowHash

             match scan.

          2) We do an all-AMPs RETRIEVE step from OB.t2 by way of an

             all-rows scan with no residual conditions into Spool 2

             (all_amps), which is duplicated on all AMPs.  The size of

             Spool 2 is estimated with low confidence to be 4 rows (100

             bytes).  The estimated time for this step is 0.03 seconds.

     5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an

        all-rows scan, which is joined to OB.t1 by way of an all-rows scan

        with no residual conditions.  Spool 2 and OB.t1 are joined using a

        product join, with a join condition of ("OB.t1.a1 <> a2").  The

        input table OB.t1 will not be cached in memory, but it is eligible

        for synchronized scanning.  The result goes into Spool 1

        (all_amps), which is redistributed by the hash code of (OB.t2.a2)

        to all AMPs.  The result spool file will not be cached in memory.

        The size of Spool 1 is estimated with no confidence to be

        707,106,782 rows (17,677,669,550 bytes).  The estimated time for

        this step is 1 hour and 16 minutes.

     6) We do a SORT to order Spool 1 by row hash.

     7) We do an all-AMPs MERGE into OB.t1 from Spool 1 (Last Use).

     8) We spoil the parser's dictionary cache for the table.

     9) Finally, we send out an END TRANSACTION step to all AMPs involved

        in processing the request.

     -> No rows are returned to the user as the result of statement 1.

        No rows are returned to the user as the result of statement 2.

Example : Target Table Composite Primary Index

The following example shows the necessity of specifying an equality condition on the primary index (and also on the partitioning column set if the target table has a PPI) in the ON clause for a target table with a composite primary index:

Suppose you create the following two tables:

     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 two MERGE requests are both valid:

     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);

     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 INTO (x1,y1,z1) VALUES (z2+10,y2+20,x2);

The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2, but the INSERT specification updates y2 for x1 rather than duplicating the ON clause specification, so it returns an error to the requestor:

     MERGE INTO t1

     USING t2

       ON x1=z2 AND y1=y2

     WHEN MATCHED THEN

       UPDATE SET z1=10

     WHEN NOT MATCHED THEN

       INSERT INTO (x1,y1,z1) VALUES (y2,z2,x2);

The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2+10, but the INSERT specification updates y2+20 for x1 rather than z2+10:

     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);

Example : ON Clause Conditions Must Be ANDed With The Primary Index and Partitioning Column Equality Constraints

The following examples show the proper and improper specification of ON clause conditions in a MERGE request.

Consider the following table definitions, with t1 being the target relation and t2 being the source relation for all the examples that follow:

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER,

       c1 INTEGER)

     PRIMARY INDEX (a1);

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER,

       c2 INTEGER)

     PRIMARY INDEX (a2);

The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request, b1=b2:

     MERGE INTO t1

     USING t2

       ON a1=a2 AND b1=b2

     WHEN MATCHED THEN

       UPDATE SET c1=c2

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request c1+c2=1 OR b1+b2=1.

Even though the second condition is internally disjunctive, the result it evaluates to is ANDed with the primary index condition.

     MERGE INTO t1

     USING t2

       ON a1=a2 AND (c1+c2=1 OR b1+b2=1)

     WHEN MATCHED THEN

       UPDATE SET c1=c2;

The following example is not valid. It aborts and returns an error message to the requestor because the primary index equality constraint a1=a2 is ORed with the other specified condition in the request, c1=c2.

The primary index equality constraint, as well as the partitioning column equality constraint if the target table has a partitioned primary index, must always be ANDed to any other conditions you specify in the ON clause.

     MERGE INTO t1

     USING t2

       ON a1=a2 OR b1=b2

     WHEN MATCHED THEN

       UPDATE SET c1=c2;

Example : For a PPI Table, the ON Clause Must Specify a Condition on the Partitioning Column and the INSERT Specification Must Match

When the target table of a MERGE operation has a partitioned primary index, the ON clause of the MERGE request must specify a condition on the partitioning column of the PPI table and the order of the columns in the INSERT clause must be the same as the order you specify in the ON clause.

Consider the following 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);

You want to use the following MERGE request to insert or update rows in t1, which has a PPI defined on x1 and a partitioning expression defined on 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 request is successful because you have defined a condition on y1 in the ON clause (y1=y2) and the specified order of columns in the INSERT clause matches the ordering specified in the ON clause.

Example : Incorrect Examples Because of ON Clause Errors or Mismatches Between the ON Clause and the INSERT Specification

The following MERGE request fails because its ON clause specifies a condition of x1=z2, but its INSERT clause substitutes y2 for x1:

     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 fails because its ON clause specifies a condition of x1=z2+10, but its INSERT clause inserts y2+20 for x1:

     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);

Example

Consider the following 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)

     UNIQUE INDEX (y2);

When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause specifies an equality condition on the primary index of the target table as a constant, the INSERT specification might or might not match the constant value specified in the ON clause. Either specification is valid, as the following two requests show.

The following MERGE request is valid even though the primary index value specified in the INSERT specification does not match the ON clause primary index specification:

     MERGE INTO t1

     USING (SELECT *

            FROM t2

            WHERE y2=10) AS s

       ON x1=10

     WHEN MATCHED THEN

       UPDATE SET z1=z2

     WHEN NOT MATCHED THEN

       INSERT (x2, y2, z2);

The following MERGE request is valid because the primary index value specified in the INSERT specification matches the ON clause primary index specification:

     MERGE INTO t1

     USING (SELECT x2, y2, z2

            FROM t2

            WHERE y2=10) AS s

       ON x1=10 AND y1=z2

     WHEN MATCHED THEN

       UPDATE SET z1=10

     WHEN NOT MATCHED THEN

       INSERT (10, z2, x2);

When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause has an equality condition on the primary index of the target table that is not a constant, the INSERT clause primary index specification must match the primary index value for the target table specified in the ON clause. The UPDATE specification also must not update the primary index of the target table.

The following MERGE request is valid because the primary index value specified in the INSERT specification, y2, matches the primary index value specified in the ON clause, and the UPDATE specification does not update the primary index of t1:

     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

     WHEN NOT MATCHED THEN

       INSERT (y2, z2, x2);

The following MERGE request fails because the source relation is not guaranteed to be a single row. The failure to guarantee a single row occurs because z2 is neither a UPI nor a USI, so the INSERT specification must match the ON clause specification, which it does not.

     MERGE INTO t1

     USING (SELECT x2, y2, z2

            FROM t2

            WHERE z2=10) AS s

       ON x1=10 AND y1=20

     WHEN MATCHED THEN

       UPDATE SET z1=10

     WHEN NOT MATCHED THEN

       INSERT (y2, z2, x2);

Example

You cannot update primary index or PPI partitioning column unless the source is a valid single‑row subquery.

Suppose you have created the following tables:

     CREATE TABLE t1 (

       x1 INTEGER,

       y1 INTEGER,

       z1 INTEGER)

     PRIMARY INDEX(x1);

     CREATE TABLE t2 (

       x2 INTEGER,

       y2 INTEGER,

       z2 INTEGER)

     PRIMARY INDEX(x2)

     UNIQUE INDEX(y2);

The following case is valid:

     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 case is not valid because while the subquery guarantees a single row, no constant in specified in the ON clause equality condition, so you cannot update the primary index as this request attempts to do:

     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);

Example

The primary index of the target table cannot be an identity column if you specify an INSERT clause even when the ON and INSERT clauses are valid.

However, if the source relation is created from a single row subquery, or if you do not specify an INSERT clause, then the target table primary index can also be an identity column.

Consider the following rules and examples based on these table definitions:

     CREATE TABLE t1 (

       x1 INTEGER GENERATED ALWAYS AS IDENTITY,

       y1 INTEGER,

       z1 INTEGER)

     PRIMARY INDEX(x1);

     CREATE TABLE t2 (

       x2 INTEGER,

       y2 INTEGER,

       z2 INTEGER)

     PRIMARY INDEX(x2)

     UNIQUE INDEX(y2);

The rules, explicitly stated, are as follows:

If you do not specify a WHEN NOT MATCHED THEN INSERT clause, the MERGE request is valid because there is no attempt to insert a value into x1, which is both the primary index of t1 and an identity column.

For example, the following MERGE request is valid because no WHEN NOT MATCHED THEN INSERT clause is specified:

     MERGE INTO t1

     USING (SELECT x2, y2, z2

            FROM t2

            WHERE y2 = 1)

       ON x1 = x2

     WHEN MATCHED THEN

       UPDATE SET y1 = y2;

If you do specify a WHEN NOT MATCHED THEN INSERT clause, the MERGE request fails and returns an error to the requestor because an attempt is made to insert a value into x1, which is both the primary index of t1 and an identity column.

For example, the following MERGE request fails because it specifies a WHEN NOT MATCHED THEN INSERT clause that attempts to insert the value of x2 into x1, which is both the primary index of t1 and an identity column:

     MERGE INTO t1

     USING (SELECT x2, y2, z2

            FROM t2

            WHERE y2 = 1)

       ON x1 = x2

     WHEN MATCHED THEN

       UPDATE SET y1 = y2

     WHEN NOT MATCHED THEN

       INSERT (x2, y2, z2);

Example

The following example fails because the INSERT specification of the WHEN NOT MATCHED clause specifies a column, z1, from the target table t1, which is an illegal operation.

     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);

Example : Failures Caused By Referencing Tables Other Than the Source or Target in the ON, WHEN MATCHED, or WHEN NOT MATCHED Clauses

The following examples fail because they reference a table other than the source or target table in either their ON, WHEN MATCHED, or WHEN NOT MATCHED clauses.

The following example fails because table t4 is neither the derived source table s nor the target table t1.

     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

       SELECT (y2, z2, x2);

The following example fails because table t3 is neither the derived source table s nor the target table t1. Even though t3 is specified in the USING source table subquery, it violates the restriction that only source and target tables can be referenced in an ON clause.

     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);

Example : Specifying the Partitioning Column Set in the ON Clause When the Target Relation Has a Partitioned Primary Index

Consider the following table definitions (t1 is a PPI table and it is partitioned on column b1).

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER,

       c1 INTEGER)

     PRIMARY INDEX (a1)

     PARTITION BY b1;

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER,

       c2 INTEGER)

     PRIMARY INDEX (a2);

The following MERGE request is valid because it specifies the partitioning column, b1, of the target table, t1, in its ON clause:

     MERGE INTO t1

       USING t2

       ON a1=a2 AND b1=b2

     WHEN MATCHED THEN

       UPDATE SET c1=c2

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

The following MERGE request aborts and returns an error message to the requestor because it does not specify the partitioning column of the target table, b1, in its ON clause:

     MERGE INTO t1

       USING t2

       ON a1=a2

     WHEN MATCHED THEN

       UPDATE SET c1=c2

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

The following MERGE request aborts and returns an error message to the requestor because its INSERT specification orders columns b2 and c2 in a different sequence than they were specified in its ON clause. The INSERT specification must always match the ON clause constraints on the primary index of the target table, and its partitioning column set if the target table has a PPI.

     MERGE INTO t1

       USING t2

       ON a1=a2 AND b1=b2

     WHEN MATCHED THEN

       UPDATE SET c1=c2

     WHEN NOT MATCHED THEN

       INSERT (a2, c2, b2);

If the target table is a PPI table, the values of the partitioning columns must also be specified in search_condition, and the INSERT clause must specify the same partitioning column values as search_condition.

Example : You Cannot Substitute the System‑Derived PARTITION Column For the Partitioning Column Set For a MERGE Operation With a PPI Target Table

Assume you have defined the following source and target relation definitions:

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER,

       c1 INTEGER)

     PRIMARY INDEX (a1)

     PARTITION BY b1;

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER,

       c2 INTEGER)

     PRIMARY INDEX (a2),

     UNIQUE INDEX(b2);

The following example fails because you cannot substitute the system‑derived PARTITION column for the partitioning column set of the target relation in the ON clause.

     MERGE INTO t1

       USING t2

       ON a1=a2 AND t1.PARTITION=10

     WHEN MATCHED THEN

       UPDATE SET b1=10

     WHEN NOT MATCHED THEN

       INSERT (a2, b2, c2);

This MERGE request is valid because it specifies conditions on the primary index of the target table, a1, and its partitioning column, b1:

     MERGE INTO t1

       USING t2

       ON a1=a2 AND b1=10

     WHEN MATCHED THEN

       UPDATE SET c1=c2;

The following MERGE request, again written against the same set of source and target tables, is not valid because it fails to specify the partitioning column for the target table, b1, in its ON clause.

     MERGE INTO t1

       USING t2

       ON a1=a2

     WHEN MATCHED THEN

       UPDATE SET c1=c2;

You can specify a system‑derived PARTITION column‑based condition in the ON clause, but only as a residual condition. For example, the following example works correctly because the primary index equality condition a1=a2 and the target table partitioning column condition b1=10 are both specified. The system treats the additional t1.PARTITION condition, t1.PARTITION=25 as a residual condition only.

Example : With a Guaranteed Single‑Row Source Relation, You Do Not Need To Specify the Partitioning Column Set

Because the following MERGE request guarantees a single‑row source relation by specifying an equality condition on the USI of target PPI table t1, it is not necessary to specify the partitioning column set:

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER,

       c1 INTEGER)

     PRIMARY INDEX (a1)

     PARTITION BY b1;

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER,

       c2 INTEGER)

     PRIMARY INDEX (a2),

     UNIQUE INDEX(b2);

     MERGE INTO t1

       USING (SELECT *

              FROM t2

              WHERE b2=10) AS s

       ON a1=1

     WHEN MATCHED THEN

       UPDATE SET c1=c2

     WHEN NOT MATCHED THEN

       INSERT (c2, b2, c2);

Example : Using the Target Table as the Source Table

The following example uses target table t1 as its source table:

     MERGE INTO t1

       USING t1 AS s

       ON t1.a1 = s.a1

     WHEN MATCHED THEN

       UPDATE SET b1 = 10

     WHEN NOT MATCHED THEN

       INSERT VALUES (s.a1, s.b1, s.c1);

Example : Using the BEGIN Period Bound Function as a Condition When Merging Into a PPI Table

Assume you define the following two tables.

     CREATE SET TABLE testing.t11 (

       a INTEGER,

       b DATE FORMAT 'YY/MM/DD',

       c DATE FORMAT 'YY/MM/DD')

     PRIMARY INDEX (a);

     CREATE SET TABLE testing.t12 (

       a INTEGER,

       b PERIOD(DATE),

       c INTEGER)

     PRIMARY INDEX (a)

     PARTITION BY RANGE_N((BEGIN(b) BETWEEN DATE '2009-01-01'

                                    AND     DATE '2011-12-31'

                                    EACH INTERVAL '1' MONTH);

The following MERGE request merges rows from t11 in to t12.

     MERGE INTO t12

       USING t11

       ON  t12.a = t11.a

       AND BEGIN (t12.b) = t11.b

     WHEN MATCHED THEN

       UPDATE SET c= 4

     WHEN NOT MATCHED THEN

       INSERT VALUES (t11.a, PERIOD(t11.b), 4);

Example : Using the BEGIN and END Period Bound Functions as Conditions When Merging Into a PPI Table

Assume you define the following two tables:

     CREATE SET TABLE testing.t21 (

       a INTEGER,

       b DATE FORMAT 'YY/MM/DD',

       c DATE FORMAT 'YY/MM/DD')

     PRIMARY INDEX (a);

     CREATE SET TABLE testing.t22 (

       a INTEGER,

       b PERIOD (DATE))

     PRIMARY INDEX (a)

     PARTITION BY (CASE_N((END(b))<= DATE '2008-03-31',

                          (END(b))<= DATE '2008-06-30',

                          (END(b))<= DATE '2008-09-30',

                          (END(b))<= DATE '2008-12-31'),

                          CASE_N((BEGIN(b))> DATE '2008-12-31')

                                 (BEGIN(b))> DATE '2008-09-30',

                                 (BEGIN(b))> DATE '2008-06-30',

                                 (BEGIN(b))> DATE '2008-03-31',);

The following MERGE request merges rows from t11 in to t22.

     MERGE INTO t22

       USING t21

       ON t22.a = t21.a

       AND BEGIN(t22.b) = t21.b

       AND END(t22.b) = t21.c

     WHEN MATCHED THEN

       UPDATE SET c=4

     WHEN NOT MATCHED THEN

       INSERT VALUES (t21.a, PERIOD(t21.b, t21.c), 4);

Example : Failure Because the Matching Condition is Defined on a PERIOD Bound Function

This example fails because the matching condition of the MERGE request is defined on a BEGIN Period bound function.

     CREATE TABLE source(

       a INTEGER,

       b PERIOD(DATE),

       c INTEGER);

     CREATE TABLE target(

       i INTEGER,

       j PERIOD(DATE),

       k INTEGER)

     PRIMARY INDEX(i)

     PARTITION BY Begin(j);

     INSERT INTO source(1, PERIOD(DATE, UNTIL_CHANGED), 1);

     MERGE INTO target

       USING source

       ON a=i AND BEGIN(j) = END(b)

     WHEN MATCHED THEN

       UPDATE SET k=c  ;

Example : Invoking an SQL UDF From a MERGE Request

This example shows how to invoke an SQL UDF named value_expression at several points within a MERGE request.

     MERGE INTO t1

     USING (SELECT a2, b2, c2

            FROM t2

            WHERE test.value_expression(b2, c2))

                  source_tbl(a2, b2, c2)

     ON a1 = source_tbl.a2 AND

        b1 = test.value_expression(source_tbl.b2, source_tbl.c2)

     WHEN MATCHED THEN

     UPDATE SET b1 = b2, c1 = test.value_expression(2,3)

     WHEN NOT MATCHED THEN

     INSERT (a2, test.value_expression(4,5), c2);

Example : Executing a MERGE Update Request When Both the Target Table and the Source Table Have Row‑Level Security Constraints

Assume that:

The user logged onto this session has the OVERRIDE UPDATE CONSTRAINT row‑level security privilege on table_1.

Both table_1 and table_2 have the same set of row‑level security constraints.

The update of table_1 is valid and Teradata Database takes the constraint values for target table table_1, which are not specified in the request, from the constraint values defined for source table table_2.

     MERGE INTO table_1 AS target

     USING table_2 AS source

     ON (target.col_1 = source.col_1)

     WHEN MATCHED THEN

     UPDATE SET level = source.col_2;

Example : Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request)

This example show how the SELECT constraint predicates are applied to the source rows when a user that does not have the required OVERRIDE privileges attempts to execute a MERGE request on a table that has the row-level security SELECT constraint. The SELECT constraint filters out the rows that the user submitting the MERGE request is not permitted to view. The SELECT constraint predicates are added to the MATCH condition.

An EXPLAIN statement is used to show the steps involved in the execution of the request and the outcome of the application of the constraint predicates.

Table Definitions

The statements used to create the tables in this example are:

     CREATE TABLE rls_src_tbl(
       col1 INT,
       col2 INT, 
       classification_levels   CONSTRAINT,
       classification_categories CONSTRAINT);

     CREATE TABLE rls_tgt_tbl(
       col1 INT,
       col2 INT, 
       classification_levels   CONSTRAINT,
       classification_categories CONSTRAINT);

User’s Session Constraint Values

The user’s sessions constraint values are:

     Constraint1Name LEVELS

     Constraint1Value 2

     Constraint3Name CATEGORIES

     Constraint3Value '90000000'xb

EXPLAIN Statement

This EXPLAIN statement is used to show the steps involved in the execution of the MERGE request and the outcome of the application of the SELECT constraint predicates.

       EXPLAIN MERGE INTO rls_tgt_tbl

       USING  rls_src_tbl

       ON (rls_tgt_tbl.col1=1)

       WHEN MATCHED THEN

          UPDATE SET col2=3

       WHEN NOT MATCHED THEN

       INSERT (1,1,rls_src_tbl.levels,rls_src_tbl.categories);

EXPLAIN Text

The system returns this EXPLAIN text.

 *** Help information returned. 24 rows.

 *** Total elapsed time was 8 seconds.

Explanation

---------------------------------------------------------------------------

  1) First, we lock a distinct RS."pseudo table" for write on a RowHash

     to prevent global deadlock for RS.rls_tgt_tbl.

  2) Next, we lock a distinct RS."pseudo table" for read on a RowHash

     to prevent global deadlock for RS.rls_src_tbl.

  3) We lock RS.rls_tgt_tbl for write, and we lock RS.rls_src_tbl for

     read.

  4) We do an all-AMPs RETRIEVE step from RS.rls_src_tbl by way of an

     all-rows scan with a condition of ("((SYSLIB.SELECTCATEGORIES (

     '90000000'XB, RS.rls_src_tbl.categories ))= 'T') AND

     ((SYSLIB.SELECTLEVEL (2, RS.rls_src_tbl.levels ))= 'T')") into

     Spool 1 (used to materialize view, derived table or table function

     rls_src_tbl) (all_amps), which is redistributed by hash code to

     all AMPs.  Then we do a SORT to order Spool 1 by row hash.  The

     size of Spool 1 is estimated with no confidence to be 1 row (31

     bytes).  The estimated time for this step is 0.03 seconds.

  5) We do an all-AMPs merge with matched updates and unmatched inserts

     into RS.rls_tgt_tbl from Spool 1 (Last Use) with a condition of (

     "(((SYSLIB.SELECTCATEGORIES ('90000000'XB, {RightTable}.CATEGORIES

     ))= 'T') AND ((SYSLIB.SELECTLEVEL (2, {RightTable}.LEVELS ))=

     'T')) AND (RS.rls_tgt_tbl.col1 = Field_4)").  The number of rows

     merged is estimated with no confidence to be 1 row.

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

MERGE Related Information

“INSERT/INSERT … SELECT” on page 370

“UPDATE” on page 490

“UPDATE (Upsert Form)” on page 515

“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