Neoview SQL Reference Manual (R2.4 SP2)

This example updates column b if a is found. If a is not found, nothing is done.
MERGE INTO t ON a = 10
WHEN MATCHED THEN UPDATE SET b = 20
This example inserts values if a is not found. If a is found, nothing is done.
MERGE INTO t ON a = 10
WHEN NOT MATCHED THEN INSERT VALUES (10, 30)
Restrictions
• A merged table cannot be a view.
• Merge is not allowed if the table has triggers, materialized views, or constraints.
• Merge is not allowed with SET ON ROLLBACK.
• The key value specified in the on-clause and the VALUE clause must be the same. This
statement is not allowed:
CREATE TABLE t (a int not null, b int not null, primary key(a));
MERGE INTO t ON a = 10
WHEN NOT MATCHED THEN INSERT VALUES (20, 30)
• The on-clause cannot contain a subquery. This statement is not allowed:
MERGE INTO t ON a = (SELECT a FROM t1) WHEN ...
• The UPDATE SET clause in a MERGE statement cannot contain a subquery. This statement
is not allowed:
MERGE INTO t ON a = 1 WHEN MATCHED THEN SET b = (SELECT a FROM t1)
• The INSERT VALUES clause in a MERGE statement cannot contain a subquery. This
statement is not allowed:
MERGE INTO t ON a = 1 WHEN NOT MATCHED THEN INSERT VALUES ((SELECT a FROM t1))
• The column being updated cannot be a clustering key.
• Use of a non-unique on-clause for a MERGE update is allowed only if there is no INSERT
clause.
MERGE INTO t USING (SELECT a,b FROM t1) x ON t.a=x.a
WHEN MATCHED THEN UPDATE SET b=x.b;
In this example, t.x=x.a is not a fully qualified unique primary key predicate.
• Use of a non-unique on-clause for a MERGE delete is allowed only if there is no INSERT
clause.
MERGE INTO t USING (SELECT a,b FROM t1) x ON t.a=x.a
WHEN MATCHED THEN DELETE;
MERGE From One Table Into Another
The MERGE statement can be used to upsert all matching rows from the source table into the
target table. Each row from the source table is treated as the source of a single upsert statement.
The using-clause contains the select-query whose output is used as the source to the
MERGE statement.
The source select-query must be renamed using the AS clause. For example:
MERGE INTO t ON col = Z.X
USING (select-query) AS Z(X)
WHEN MATCHED THEN . . .
For each row selected out of the select-query, the MERGE statement is evaluated. Values selected
are used in the ON clause to join with the column of the merged table. If the value is found, it is
updated. If it is not found, the insert is done. The restrictions are the same as those for “Upsert
Using Single Row” (page 150).
MERGE INTO Statement 151