Rules for Primary Condition in the ON Clause - 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 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;