Example: Incorrect Examples Because of ON Clause Errors or Mismatches Between the ON Clause and the INSERT Specification - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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