MERGE
<< INSERT | FB 2.5 Language Reference | SELECT >>
<< INSERT | FB 2.1 Language Reference | SELECT >>
MERGE
Added in: 2.1
Description
Merges data into a table or view. The source may a table, view or derived table (i.e. a parenthesized SELECT statement or CTE). 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 provided condition and the WHEN clause(s). The condition will typically contain a comparison of fields in the source and target relations.
Syntax
MERGE INTO {tablename | viewname} [[AS] alias]
USING {tablename | viewname | (select_stmt)} [[AS] alias]
ON condition
WHEN MATCHED THEN UPDATE SET colname = value [, colname = value ...]
WHEN NOT MATCHED THEN INSERT [(<columns>)] VALUES (<values>)
<columns> ::= colname [, colname ...]
<values> ::= value [, value ...]
Note: It is allowed to provide only one of the WHEN clauses.
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)
Note: WHEN NOT MATCHED should be interpreted from the point of view of the source (the relation in the USING clause). That is: if a source record doesn't have a match in the target table, the INSERT clause is executed. Conversely, records in the target table without a matching source record don't trigger any action.
Warning: If the WHEN MATCHED clause is present and multiple source records match the same record in the target table, the UPDATE clause is executed for all the matching source records, each update overwriting the previous one. This is non-standard behaviour: SQL-2003 specifies that in such a case an exception must be raised.
See also:
MERGE statement
MERGE
back to top of page
<< INSERT | FB 2.5 Language Reference | SELECT >>
<< INSERT | FB 2.1 Language Reference | SELECT >>
