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

<columns>  ::=  colname [, colname ...]
<values>   ::=  value   [, value   ...]

Table 6.15. Arguments for the MERGE Statement Parameters

Argument	Description
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
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
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.

Notes

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.