MERGE

Available in: DSQL, PSQL

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.