Neoview SQL Reference Manual (R2.2)

MERGE INTO Statement
• “Syntax Description of MERGE INTO”
• “Considerations for MERGE INTO”
The MERGE INTO statement provides upsert, reflexive updates, and updates from one table
into another..
MERGE INTO table [using-clause
] on-clause {[when-matched-clause] [when-not-matched-clause]}
using-clause is:
USING (select-query)
AS derived-table-name [derived-column-names]
on-clause is:
ON predicate
when-matched-clause is:
WHEN MATCHED THEN UPDATE SET set-clause
set-clause is:
SET ...
when-not-matched-clause is:
WHEN NOT MATCHED THEN INSERT insert-vales-list
insert-values-list is:
[(column1, ..., columnN ] VALUES (value1, ..., valueN)
Syntax Description of MERGE INTO
table
is the ANSI logical name for the table.
Considerations for MERGE INTO
Upsert Using Single Row
An upsert statement allows you specify a set of values that should be updated if the row is found,
and another set of values to be inserted if the row is not found. The search condition must select
exactly one row that is to be updated..
At least one of the when-matched or when-not-matched must be specified. If a when-matched
clause is present, all the columns in the SET clause are updated. If the when-not-matched
clause is present and columns are explicitly specified in the INSERT clause, those columns are
inserted. Missing columns are updated using the default value for that column.
This example updates column b to 20 if the row with key 10 already exists. A new row (10, 30)
is inserted if the row does not exist.
MERGE INTO t ON a = 10
WHEN MATCHED THEN UPDATE SET b = 20
WHEN NOT MATCHED THEN INSERT VALUES (10, 30)
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)
MERGE INTO Statement 129