Firebird Documentation IndexFirebird 3.0.6 Release NotesData Manipulation Language (DML) → Supplemental SQL 2008 Features for MERGE
Firebird Home Firebird Home Prev: Data Manipulation Language (DML)Firebird Documentation IndexUp: Data Manipulation Language (DML)Next: Window (Analytical) Functions

Supplemental SQL 2008 Features for MERGE

Adriano dos Santos Fernandes

In summary, support for MERGE was supplemented with the introduction of these features:

The purpose of MERGE is to read data from the source and INSERT or UPDATE in the target table according to a condition. It is available in 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>...
		<returning clause>

  <merge when> ::=
	<merge when matched> |
	<merge when not matched>

  <merge when matched> ::=
	WHEN MATCHED [ AND <condition> ] THEN
		{ UPDATE SET <assignment list> | DELETE }

  <merge when not matched> ::=
	WHEN NOT MATCHED [ AND <condition> ] THEN
		INSERT [ <left paren> <column list> <right paren> ]
			VALUES <left paren> <value list> <right paren>
      

Rules

At least one of <merge when matched> or <merge when not matched> should be specified.

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)
      

Notes

A right join is made between the INTO (left-side) and USING tables using the condition. UPDATE is called when a record exists in the left table (INTO), otherwise INSERT is called.

As soon as it is determined whether or not the source matches a record in the target, the set formed from the corresponding (WHEN MATCHED / WHEN NOT MATCHED) clauses is evaluated in the order specified, to check their optional conditions. The first clause whose condition evaluates to true is the one which will be executed, and the subsequent ones will be ignored.

If no record is returned in the join, INSERT is not called.

Prev: Data Manipulation Language (DML)Firebird Documentation IndexUp: Data Manipulation Language (DML)Next: Window (Analytical) Functions
Firebird Documentation IndexFirebird 3.0.6 Release NotesData Manipulation Language (DML) → Supplemental SQL 2008 Features for MERGE