Neoview SQL Reference Manual (R2.5)

MERGE INTO Statement
• “Syntax Description of MERGE INTO”
• “Considerations for MERGE INTO”
• “Example of MERGE INTO”
The MERGE INTO statement:.
• Updates a table if the row exists and inserts into a table if the row does not exist. This is
upsert functionality.
• Updates (merges) matching rows from one table to 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
WHEN MATCHED THEN DELETE
set-clause is:
SET ...
when-not-matched-clause is:
WHEN NOT MATCHED THEN INSERT insert-values-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 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 clauses 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)
MERGE INTO Statement 151