Neoview SQL Reference Manual (R2.3)

MERGE INTO Statement
• “Syntax Description of MERGE INTO”
• “Considerations for MERGE INTO”
The MERGE INTO statement:.
• Updates a table if the row exists and inserts if it does not. This is upsert functionality.
• Performs reflexive updates where columns are updated incrementally.
• Updates (merges) matching rows from one table to another.
• Upserts, reflexive updates, and multi column set updates using rowsets.
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.
ON predicate
ON predicate must be a unique predicate on the clustering key of the table. The clustering
key can be a single or multi-column key.
Considerations for MERGE INTO
Upsert Using Single Row
A MERGE INTO 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
132 SQL Statements