Restrictions for Primary Condition in the ON clause - Teradata Database

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.