Example: ON Clause Conditions Must Be ANDed with Primary Index and Partitioning Column Equality Constraints - Teradata Vantage

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.

The second condition is internally disjunctive, but its result 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 invalid, and stops 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 and the partitioning column equality constraint if the target table has a partitioned primary index, must 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;