Merge (SQL)

A relational database management system uses SQL MERGE (aka. 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:2003 standard.

The usage is as follows:

MERGE INTO table_name USING table_name ON (condition)

WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]

WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

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 which can be used to achieve the same effect. It also supports REPLACE INTO... syntax, which has a similar (although subtly different) effect.

External links

• Oracle10g Release 2 documentation on MERGE
• DB2 v9 MERGE statement