Consider the following 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) UNIQUE INDEX (y2);
When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause specifies an equality condition on the primary index of the target table as a constant, the INSERT specification might or might not match the constant value specified in the ON clause. Either specification is valid, as the following two requests show.
The following MERGE request is valid even though the primary index value specified in the INSERT specification does not match the ON clause primary index specification:
MERGE INTO t1 USING (SELECT * FROM t2 WHERE y2=10) AS s ON x1=10 WHEN MATCHED THEN UPDATE SET z1=z2 WHEN NOT MATCHED THEN INSERT (x2, y2, z2);
The following MERGE request is valid because the primary index value specified in the INSERT specification matches the ON clause primary index specification:
MERGE INTO t1 USING (SELECT x2, y2, z2 FROM t2 WHERE y2=10) AS s ON x1=10 AND y1=z2 WHEN MATCHED THEN UPDATE SET z1=10 WHEN NOT MATCHED THEN INSERT (10, z2, x2);
When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause has an equality condition on the primary index of the target table that is not a constant, the INSERT clause primary index specification must match the primary index value for the target table specified in the ON clause. The UPDATE specification also must not update the primary index of the target table.
The following MERGE request is valid because the primary index value specified in the INSERT specification, y2, matches the primary index value specified in the ON clause, and the UPDATE specification does not update the primary index of t1:
MERGE INTO t1 USING (SELECT x2, y2, z2 FROM t2 WHERE y2=10) AS s ON x1=y2 AND y1=z2 WHEN MATCHED THEN UPDATE SET z1=10 WHEN NOT MATCHED THEN INSERT (y2, z2, x2);
The following MERGE request fails because the source relation is not guaranteed to be a single row. The failure to guarantee a single row occurs because z2 is neither a UPI nor a USI, so the INSERT specification must match the ON clause specification, which it does not.
MERGE INTO t1 USING (SELECT x2, y2, z2 FROM t2 WHERE z2=10) AS s ON x1=10 AND y1=20 WHEN MATCHED THEN UPDATE SET z1=10 WHEN NOT MATCHED THEN INSERT (y2, z2, x2);