Merge (SQL)

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE or DELETE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded[citation needed] in the SQL:2008 standard.

Usage

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

A right join is employed over the Target (the INTO table) and the Source (the USING table / view / sub-query)--where Target is the left table and Source is the right one. The four possible combinations yield these rules:

If multiple Source rows match a given Target row, an error is mandated by SQL:2003 standards. You cannot update a Target row multiple times with a MERGE statement

Implementations

Database management systems PostgreSQL, Oracle Database, IBM Db2, Teradata, EXASOL, Firebird, CUBRID, H2, HSQLDB, MS SQL (Transact-SQL), MonetDB, Vectorwise, Apache Derby, and Spark SQL support the standard syntax. Some also add non-standard SQL extensions.

Synonymous

Some database implementations adopted the term upsert (a portmanteau of update and insert) to a database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. This synonym is used in PostgreSQL (v9.5+) and SQLite (v3.24+).

Upsert most commonly refers to non-standard SQL extensions that provide this behavior, most commonly via an expansion of the INSERT statement:

INSERT ... ON DUPLICATE KEY UPDATE
A MySQL INSERT extension which can be used to achieve a similar effect with the limitation that the join between target and source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO MERGE standard.
Also supported by CUBRID.
INSERT IGNORE
A MySQL INSERT extension which tells the server to ignore "duplicate key" errors and go on (existing rows will not be inserted or updated, but all new rows will be inserted).
INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action
A PostgreSQL syntax also used by SQLite.

Other expansions used to reach a similar effect include:

REPLACE INTO
A MySQL statement which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one.
Also supported by CUBRID.
Also supported by SQLite under the names of REPLACE INTO and INSERT OR REPLACE INTO.
UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)]
A Firebird extension. Does not provide the option to take different actions on insert versus update (e.g. setting a new sequence value only for new rows, not for existing ones.)

In addition:

Extensions

Non-standard extensions to the standard MERGE statement include:

Firebird supports MERGE INTO though fails to throw an error when there are multiple Source data rows.

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

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

Other data structures

NoSQL

A similar concept is applied in some NoSQL databases.

Most key-value databases, staring from the classical Unix dbm, have a "set" or "store" command that can simply overwrite the existing value.

MongoDB is a document-oriented database, where each document is a BSON. update() normally take a query and a set of operations and applies the operations on the matching entry; an example operation is $set, which gives the simple overwriting behavior for setting a JSON path. It also has an upsert mode, which specifies that a new value should be inserted if the query finds nothing.

Programming languages

ECMAScript 2026 formalizes an "upsert" proposal, which entails a getOrInsert(key, default) API. If the Map contains the requested key, this API simply returns the corresponding value. If the map does not contain the requested key, it would set the entry to the provided default value and return the default value.

See also

References

  1. ↑ "E.1. Release 15". PostgreSQL Documentation. 13 October 2022. Archived from the original on 13 October 2022. Retrieved 13 October 2022.
  2. ↑ "MERGE INTO (Delta Lake on Databricks)".
  3. ↑ "PostgreSQL Upsert Using INSERT ON CONFLICT statement". PostgreSQL Tutorial. Archived from the original on Nov 28, 2022.
  4. 1 2 "upsert", SQLite, visited 6-6-2018.
  5. ↑ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
  6. ↑ CUBRID :: Data Manipulation Statements :: Insert :: ON DUPLICATE KEY UPDATE Clause
  7. ↑ "MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax". Retrieved 29 October 2013.
  8. ↑ PostgreSQL INSERT page
  9. ↑ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
  10. ↑ CUBRID :: Data Manipulation Statements :: Replace
  11. ↑ "SQL As Understood By SQLite: INSERT". Retrieved 2012-09-27.
  12. ↑ "UPSERT VALUES".
  13. ↑ "UPSERT SELECT".
  14. ↑ "INSERT | Databricks on AWS". docs.databricks.com. 3 August 2026.
  15. ↑ "UPSERT Statement (Apache Impala Documentation)".
  16. ↑ "MERGE (Transact-SQL)". Transact-SQL Reference (Database Engine). Microsoft Learn. Archived from the original on Jun 24, 2016.
  17. ↑ "SET (Redis)".
  18. ↑ "db.collection.update() (mongosh method) - Database Manual - MongoDB Docs". www.mongodb.com.
  19. ↑ "tc39/proposal-upsert".