Merge (SQL)

Quality 0.50 · 0 views · Updated 2 months ago

SQL statement

title: "Merge (SQL)" type: doc version: 1 created: 2026-02-28 author: "Wikipedia contributors" status: active scope: public tags: ["sql-keywords", "articles-with-example-sql-code", "sql"] description: "SQL statement" topic_path: "technology/databases" source: "https://en.wikipedia.org/wiki/Merge_(SQL)" license: "CC BY-SA 4.0" wikipedia_page_id: 0 wikipedia_revision_id: 0

::summary SQL statement ::

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

Usage

::code[lang=zetasql] 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 table) and the Source (the table / view / sub-query)--where Target is the left table and Source is the right one. The four possible combinations yield these rules:

  • If the field(s) in the Source matches the field(s) in the Target, then
  • If the field(s) in the Source does not match the field(s) in the Target, then
  • If the field(s) does not exist in the Source but does exist in the Target, then no action is performed.
  • If the field(s) does not exist in either the Source or Target, then no action is performed.

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 statement

Implementations

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

{{anchor|upsert}} 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+). It is also used to abbreviate the "MERGE" equivalent pseudo-code.

It is used in Microsoft Azure SQL Database. The SQL:2003 defines a statement that provides similar functionality. In MySQL, UPSERT operations are carried out with the (where the row is updated if already inserted) syntax. Frequently, database operations are performed in a context where multiple agents can perform queries on the same database. If the DBMS does not natively support a version of UPSERT/MERGE, the operation should be wrapped in a transaction to guarantee isolation and atomicity.

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 syntax 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 standard. It also supports syntax, which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an clause for the statement, 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).

SQLite's works similarly. It also supports as an alias for compatibility with MySQL.

Firebird supports though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version, , but the latter does not give you 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.)

IBM Db2 extends the syntax with multiple and clauses, distinguishing them with guards.

Microsoft SQL Server extends with supporting guards and also with supporting Left Join via clauses.

PostgreSQL supports merge since version 15 but previously supported merging via .

CUBRID supports statement. And supports the use of syntax. It also supports for compatibility with MySQL.

Apache Phoenix supports and UPSERT SELECT syntax.

Spark SQL supports and clauses in actions.

Apache Impala supports .

Usage in NoSQL

A similar concept is applied in some NoSQL databases.

In MongoDB the fields in a value associated with a key can be updated with an operation. The raises an error if the key is not found. In the operation it is possible to set the flag: in this case a new value is stored associated to the given key if it does not exist, otherwise the whole value is replaced.

In Redis the operations sets the value associated with a given key. Redis does not know any detail of the internal structure of the value, so an update would have no meaning. So the operation has always a set or replace semantics.

References

References

  1. (13 October 2022). "E.1. Release 15".
  2. "PostgreSQL Upsert Using INSERT ON CONFLICT statement".
  3. "[http://sqlite.org/lang_UPSERT.html upsert]", SQLite, visited 6-6-2018.
  4. "MERGE (Transact-SQL)". Microsoft Learn.
  5. [http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax]
  6. [http://dev.mysql.com/doc/refman/5.1/en/replace.html MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax]
  7. "MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax".
  8. "SQL As Understood By SQLite: INSERT".
  9. [http://www.postgresql.org/docs/current/static/sql-insert.html PostgreSQL INSERT page ]
  10. (2012-10-30). "New CUBRID 9.0.0". CUBRID Official Blog.
  11. [http://www.cubrid.org/manual/10_0/en/sql/query/insert.html#on-duplicate-key-update-clause CUBRID :: Data Manipulation Statements :: Insert :: ON DUPLICATE KEY UPDATE Clause]
  12. [http://www.cubrid.org/manual/10_0/en/sql/function/string_fn.html#replace CUBRID :: Data Manipulation Statements :: Replace]
  13. "UPSERT VALUES".
  14. "UPSERT SELECT".
  15. "MERGE INTO (Delta Lake on Databricks)".
  16. "UPSERT Statement (Apache Impala Documentation)".

::callout[type=info title="Wikipedia Source"] This article was imported from Wikipedia and is available under the Creative Commons Attribution-ShareAlike 4.0 License. Content has been adapted to SurfDoc format. Original contributors can be found on the article history page. ::

sql-keywordsarticles-with-example-sql-codesql