6.6. MERGE

Used forMerging data from a source set into a target relation

Available inDSQL, 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> ...]

Table 6.17. Arguments for the MERGE Statement Parameters
ArgumentDescription

target

Name of target relation (table or updatable view)

source

Data source. It can be a table, a view, a stored procedure or a derived table

target-alias

Alias for the target relation (table or updatable view)

source-alias

Alias for the source relation or set

join-conditions

The (ON) condition[s] for matching the source records with those in the target

tablename

Table or view name

select-stmt

Select statement of the derived table

colname

Name of a column in the target relation

value

The value assigned to a column in the target table. It is an expression that may be a literal value, a PSQL variable, a column from the source or a compatible context variable

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.

ⓘ
Notes

At least one WHEN clause must be present. Only one of each WHEN clause can be supplied. This will change in the next major version of Firebird, when compound matching conditions will be supported.

WHEN NOT MATCHED is evaluated from the source viewpoint, that is, the table or set specified in USING. It has to work this way because, if the source record does not match a target record, INSERT is executed. Of course, if there is a target record that does not match a source record, nothing is done.

Currently, the ROW_COUNT variable returns the value 1, even if more than one record is modified or inserted. For details and progress, refer to Tracker ticket CORE-4400.

🛑
ALERT : Another irregularity!

If the WHEN MATCHED clause is present and several records match a single record in the target table, an UPDATE will be executed on that one target record for each one of the matching source records, with each successive update overwriting the previous one. This behaviour does not comply with the SQL:2003 standard, which requires that this situation throw an exception (an error).

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);
☝
The Unstable Cursor Problem

Because of the way the execution of data-changing DML is implemented in Firebird, up to and including this version, the sets targeted for merging sometimes produce unexpected results. For more information, refer to The Unstable Cursor Problem in the UPDATE section.