The following example shows the necessity of specifying an equality condition on the primary index (and also on the partitioning column set for a row-partitioned table) in the ON clause for a target table with a composite primary index:
Suppose you create the following two tables:
CREATE TABLE t1 ( x1 INTEGER, y1 INTEGER, z1 INTEGER) PRIMARY INDEX (x1, y1); CREATE TABLE t2 ( x2 INTEGER, y2 INTEGER, z2 INTEGER) PRIMARY INDEX(x2, y2);
The following two MERGE requests are both valid:
MERGE INTO t1 USING t2 ON x1=z2 AND y1=y2 WHEN MATCHED THEN UPDATE SET z1=10 WHEN NOT MATCHED THEN INSERT (z2,y2,x2); 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 INTO (x1,y1,z1) VALUES (z2+10,y2+20,x2);
The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2, but the INSERT specification updates y2 for x1 rather than duplicating the ON clause specification, so it returns an error to the requestor:
MERGE INTO t1 USING t2 ON x1=z2 AND y1=y2 WHEN MATCHED THEN UPDATE SET z1=10 WHEN NOT MATCHED THEN INSERT INTO (x1,y1,z1) VALUES (y2,z2,x2);
The following MERGE request is not valid because the ON clause specifies an equality condition where x1=z2+10, but the INSERT specification updates y2+20 for x1 rather than z2+10:
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);