Merge (SQL)

A relational database management system uses SQL MERGE (upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2008 standard.

Usage

MERGE INTO table_name USING table_reference ON (condition)
  WHEN MATCHED THEN
  UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
  INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

Right join is employed over Target and Source tables. That is, rows present in Source and missed from Target do run the action, the rows missed from Source and present in Target are ignored. If multiple Source rows match a given Target row, error is mandated by SQL-2003.

Implementations

Database management systems Oracle Database, DB2, and MS SQL support the standard syntax. Some also add non-standard SQL extensions.

Other non-standard implementations

Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.

MySQL, for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE syntax^[1] which can be used to achieve the same effect. It also supports REPLACE INTO syntax^[2], which first deletes the row, if exists, and then inserts the new one.

SQLite's INSERT OR REPLACE INTO works similarly.

Firebird supports MERGE INTO though fails at throwing error when multiple Source data. Additionally a single-row version, UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)], but the latter does not give you the option to take different actions on insert vs. update (e.g. setting a new sequence value only for new rows, not for existing ones.)

IBM DB2 extends syntax with multiple WHEN MATCHED and WHEN NOT MATCHED clauses, distinguishing them with ... AND some-condition guards.

Microsoft SQL extends with supporting guards and also with supporting Left Join via WHEN NOT MATCHED BY SOURCE clauses.

References

1. ^ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
2. ^ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax

External links

• Oracle 11g Release 2 documentation on MERGE
• Firebird 2.1 documentation on MERGE
• DB2 v9 MERGE statement
• SQL Server 2008 documentation
• H2 (1.2) SQL Syntax page