{{Short description|SQL statement}} {{primary|date=September 2025}} A relational database management system uses SQL '''{{mono|MERGE}}''' (also called ''upsert'') statements to {{mono|INSERT}} new records or {{mono|UPDATE}} or {{mono|DELETE}} existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded{{Citation needed|date=January 2024}} in the SQL:2008 standard.
==Usage== <syntaxhighlight 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 ...]); </syntaxhighlight>
A right join is employed over the Target (the {{mono|INTO}} table) and the Source (the {{mono|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 the {{mono|ON}} field(s) in the Source matches the {{mono|ON}} field(s) in the Target, then {{mono|UPDATE}} * If the {{mono|ON}} field(s) in the Source does not match the {{mono|ON}} field(s) in the Target, then {{mono|INSERT}} * If the {{mono|ON}} field(s) does not exist in the Source but does exist in the Target, then no action is performed. * If the {{mono|ON}} 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 {{mono|MERGE}} statement
==Implementations== Database management systems PostgreSQL,<ref>{{cite web |title=E.1. Release 15 |url=https://www.postgresql.org/docs/15/release-15.html |website=PostgreSQL Documentation |access-date=13 October 2022 |language=en |date=13 October 2022 |url-status=live |archive-url=https://web.archive.org/web/20221013141239/https://www.postgresql.org/docs/15/release-15.html |archive-date=13 October 2022 }}</ref> 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+)<ref>{{Cite web |title=PostgreSQL Upsert Using INSERT ON CONFLICT statement |url=http://www.postgresqltutorial.com/postgresql-upsert/ |url-status=live |archive-url=https://web.archive.org/web/20221128173803/https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/ |archive-date=Nov 28, 2022 |website=PostgreSQL Tutorial}}</ref> and SQLite (v3.24+).<ref>"[http://sqlite.org/lang_UPSERT.html upsert]", SQLite, visited 6-6-2018.</ref> It is also used to abbreviate the "MERGE" equivalent pseudo-code.
It is used in Microsoft Azure SQL Database.<ref>{{Cite web |title=MERGE (Transact-SQL) |url=https://msdn.microsoft.com/en-us/library/bb510625.aspx |url-status=live |archive-url=https://web.archive.org/web/20160624122818/https://msdn.microsoft.com/en-us/library/bb510625.aspx |archive-date=Jun 24, 2016 |website=Transact-SQL Reference (Database Engine) |publisher=Microsoft Learn}}</ref> <!-- The SQL:2003 defines a {{code|MERGE}} statement that provides similar functionality. In MySQL, UPSERT operations are carried out with the {{code|INSERT ... ON DUPLICATE KEY UPDATE}} (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 {{code|lang=mysql|INSERT ... ON DUPLICATE KEY UPDATE}} syntax<ref>[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]</ref> 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 {{code|>REPLACE INTO}} syntax,<ref>[http://dev.mysql.com/doc/refman/5.1/en/replace.html MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax]</ref> which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an {{code|IGNORE}} clause for the {{code|INSERT}} statement,<ref>{{cite web|title=MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax|url=http://dev.mysql.com/doc/refman/5.5/en/insert.html|accessdate=29 October 2013}}</ref> 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 {{code|lang=sql|INSERT OR REPLACE INTO}} works similarly. It also supports {{code|REPLACE INTO}} as an alias for compatibility with MySQL.<ref>{{cite web|url=http://www.sqlite.org/lang_insert.html|title=SQL As Understood By SQLite: INSERT|accessdate=2012-09-27}}</ref>
Firebird supports {{code|MERGE INTO}} though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version, {{code|lang=sql|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 versus update (e.g. setting a new sequence value only for new rows, not for existing ones.)
IBM Db2 extends the syntax with multiple {{code|WHEN MATCHED}} and {{code|WHEN NOT MATCHED}} clauses, distinguishing them with {{code|... AND some-condition}} guards.
Microsoft SQL Server extends with supporting guards and also with supporting Left Join via {{code|lang=tsql|WHEN NOT MATCHED BY SOURCE}} clauses.
PostgreSQL supports merge since version 15 but previously supported merging via {{code|2=postgresql|INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action}}.<ref>[http://www.postgresql.org/docs/current/static/sql-insert.html PostgreSQL INSERT page ]</ref>
CUBRID supports {{code|MERGE INTO}}<ref>{{cite web|url=http://www.cubrid.org/blog/news/announcing-cubrid-9-0-with-3x-performance-increase-and-sharding-support|title=New CUBRID 9.0.0|publisher=CUBRID Official Blog|date=2012-10-30|accessdate=2012-11-08}}</ref> statement. And supports the use of {{code|lang=mysql|INSERT ... ON DUPLICATE KEY UPDATE}} syntax.<ref>[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]</ref> It also supports {{code|REPLACE INTO}} for compatibility with MySQL.<ref>[http://www.cubrid.org/manual/10_0/en/sql/function/string_fn.html#replace CUBRID :: Data Manipulation Statements :: Replace]</ref>
Apache Phoenix supports {{code|UPSERT VALUES}}<ref>{{cite web|url=https://phoenix.apache.org/language/#upsert_values|title=UPSERT VALUES}}</ref> and <code>UPSERT SELECT</code><ref>{{cite web|url=https://phoenix.apache.org/language/#upsert_select|title=UPSERT SELECT}}</ref> syntax.
Spark SQL supports {{code|UPDATE SET *}} and {{code|INSERT *}} clauses in actions.<ref>{{cite web|url=https://docs.databricks.com/spark/latest/spark-sql/language-manual/delta-merge-into.html|title=MERGE INTO (Delta Lake on Databricks)}}</ref>
Apache Impala supports {{code|UPSERT INTO ... SELECT}}.<ref>{{cite web|url=https://impala.apache.org/docs/build/html/topics/impala_upsert.html|title=UPSERT Statement (Apache Impala Documentation)}}</ref>
==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 {{code|update}} operation. The {{code|update}} raises an error if the key is not found. In the {{code|update}} operation it is possible to set the {{code|upsert}} 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 {{code|SET}} 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 {{code|SET}} operation has always a ''set or replace'' semantics.
==See also== * Join in particular: ** Join (SQL) ** join (Unix)
==References== <references/> <!-- from upsert: --> *{{cite web|title=Cross Compare of SQL Server, MySQL, and PostgreSQL|date=May 18, 2008|url=http://www.postgresonline.com/journal/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html|work= Postgres OnLine Journal|first1=Leo|last1=Hsu|first2=Regina|last2=Obe|accessdate=8 October 2010}} *{{cite book |last= Chodorow |first= Kristina |author2=Mike Dirolf |title= MongoDB: The Definitive Guide |publisher= O'Reilly |date=September 2010 |isbn= 978-1-449-38156-1}}
==External links== * [http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606 Oracle 11g Release 2 documentation] on {{code|MERGE}} * [http://www.firebirdsql.org/refdocs/langrefupd21-merge.html Firebird 2.1 documentation] on {{code|MERGE}} * [http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0010873.html DB2 v9 MERGE statement] * [http://msdn.microsoft.com/en-us/library/bb510625.aspx Microsoft SQL Server documentation] * [http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_merge_statement HSQLdb 2.0 Data Change Statements] * [http://www.h2database.com/html/grammar.html#merge H2 (1.2) SQL Syntax page]
{{SQL}}
Category:SQL keywords Category:Articles with example SQL code Category:SQL