MERGE Statement
Adriano dos Santos Fernandes
(v.2.1) This syntax has been introduced to enable a record to be either updated or inserted, according to whether or not a stated condition is met. The statement is available in both DSQL and PSQL.
Syntax Pattern
<merge statement> ::=
MERGE
INTO <table or view> [ [AS] <correlation name> ]
USING <table or view or derived table> [ [AS] <correlation name> ]
ON <condition>
[ <merge when matched> ]
[ <merge when not matched> ]
<merge when matched> ::=
WHEN MATCHED THEN
UPDATE SET <assignment list>
<merge when not matched> ::=
WHEN NOT MATCHED THEN
INSERT [ <left paren> <column list> <right paren> ]
VALUES <left paren> <value list> <right paren>
Rules for MERGE
At least one of <merge when matched> and <merge when not matched> should be specified
Neither should be specified more than once.
Note
A right join is made between the INTO and USING tables using the condition. UPDATE is called when a matching record exists in the left (INTO) table, otherwise INSERT is called.
If no record is returned from the join, INSERT is not called.
Example
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET
name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (cd.id, cd.name)
Partager