Primary Condition Must Be ANDed with Secondary Conditions - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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;