6.6. MERGE

Used for

Merging data from a source set into a target relation

Available in

DSQL, PSQL

Syntax

MERGE INTO target [[AS] target-alias]
   USING <source> [[AS] source-alias]
   ON <join-condition>
   [ WHEN MATCHED
       THEN UPDATE SET colname = <value> [, <colname> = <value> ...]]
   [ WHEN NOT MATCHED
       THEN INSERT [(<columns>)] VALUES (<values>)]
<source> ::= tablename | (<select-stmt>)
<columns> ::= colname [, colname ...]
<values> ::= <value> [, <value> ...]

Description

The MERGE statement merges data into a table or updatable view. The source may be a table, view or “anything you can SELECT from” in general. Each source record will be used to update one or more target records, insert a new record in the target table, or neither.

The action taken depends on the supplied join condition and the WHEN clause(s). The condition will typically contain a comparison of fields in the source and target relations.

Examples

MERGE INTO books b
  USING purchases p
  ON p.title = b.title and p.type = 'bk'
  WHEN MATCHED THEN
    UPDATE SET b.desc = b.desc || '; ' || p.desc
  WHEN NOT MATCHED THEN
    INSERT (title, desc, bought) values (p.title, p.desc, p.bought);

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);

MERGE INTO numbers
  USING (
    WITH RECURSIVE r(n) AS (
      SELECT 1 FROM rdb$database
      UNION ALL
      SELECT n+1 FROM r WHERE n < 200
    )
    SELECT n FROM r
  ) t
  ON numbers.num = t.n
  WHEN NOT MATCHED THEN
    INSERT(num) VALUES(t.n);