For a target table with row partitioning, the MERGE request ON clause must specify a condition on the partitioning column and its WHEN NOT MATCHED clause must match that condition.
Consider the following target and source table definitions:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX (x1)
PARTITION BY y1;
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX (x2);
The following MERGE request is valid because it specifies a condition on the partitioning
column of t1, y1=y2, and its INSERT specification inserts a value from column t2.y2 into column t1.y1:
MERGE INTO t1
USING (SELECT *
FROM t2) AS s
ON x1=x2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=z2
WHEN NOT MATCHED THEN
INSERT (x2, y2, z2);
The following MERGE request is not valid because while its ON clause specifies a valid condition of x1=z2 AND y1=y2, its INSERT specification inserts a value from column t2.y2 into column t1.x1, which does not match the ON clause condition.
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 is not valid because while its ON clause specifies a valid condition of x1=z2+10, its INSERT specification inserts the value for the expression t2.y2 + 20 into t1.x1, which does not match the ON clause condition.
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);