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:2003 standard.
Usage
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[1] which can be used to achieve the same effect. It also supports ...INSERT INTO syntax[2], which has a similar (although subtly different) effect.
SQLite's INSERT OR REPLACE INTO works similarly.
Firebird supports both MERGE INTO and 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.)
References
External links
- Oracle10g Release 2 documentation on MERGE
- DB2 v9 MERGE statement
- SQL Server 2008 documentation
- H2 (1.2) SQL Syntax page
 | This database-related article is a stub. You can help Wikipedia by expanding it. |