World Library  
Flag as Inappropriate
Email this Article

Merge (SQL)

Article Id: WHEBN0004347931
Reproduction Date:

Title: Merge (SQL)  
Author: World Heritage Encyclopedia
Language: English
Subject: SQL, Condition (SQL), Null (SQL), Edgar F. Codd, IT
Publisher: World Heritage Encyclopedia

Merge (SQL)

A relational database management system uses SQL MERGE (also called 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, and expanded in the SQL:2008 standard.


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

Right join is employed over the Target (the INTO table) and the Source (the USING table / view / sub-query). That is:

  • If rows present in the Source but missing from the Target do run the action then specifically the NOT MATCHED action
  • If rows missing from the Source and present in Target are ignored then no action is performed on the Target.

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


Database management systems Oracle Database, DB2, Teradata, EXASOL, CUBRID, MS SQL and Vectorwise support the standard syntax. Some also add non-standard SQL extensions.


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. It is also used to abbreviate the "MERGE" equivalent pseudo-code.

It is used in Microsoft SQL Azure.

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 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 REPLACE INTO syntax,[2] which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an IGNORE clause for the INSERT statement,[3] 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 INSERT OR REPLACE INTO works similarly. It also supports REPLACE INTO as an alias for compatibility with MySQL.[4]

Firebird supports MERGE INTO though fails to throw an error when there are multiple Source data rows. Additionally there is 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 versus update (e.g. setting a new sequence value only for new rows, not for existing ones.)

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

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

See also


  1. ^ MySQL :: MySQL 5.1 Reference Manual :: INSERT ... ON DUPLICATE KEY UPDATE Syntax
  2. ^ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
  3. ^ "MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax". Retrieved 29 October 2013. 
  4. ^ "SQL As Understood By SQLite: INSERT". Retrieved 2012-09-27. 
  • Hsu, Leo; Obe, Regina (May 18, 2008). "Cross Compare of SQL Server, MySQL, and PostgreSQL". Postgres OnLine Journal. Retrieved 8 October 2010. 
  • Chodorow, Kristina; Mike Dirolf (September 2010).  

External links

  • Oracle 11g Release 2 documentation on MERGE
  • Firebird 2.1 documentation on MERGE
  • DB2 v9 MERGE statement
  • SQL Server 2008 documentation
  • H2 (1.2) SQL Syntax page
This article was sourced from Creative Commons Attribution-ShareAlike License; additional terms may apply. World Heritage Encyclopedia content is assembled from numerous content providers, Open Access Publishing, and in compliance with The Fair Access to Science and Technology Research Act (FASTR), Wikimedia Foundation, Inc., Public Library of Science, The Encyclopedia of Life, Open Book Publishers (OBP), PubMed, U.S. National Library of Medicine, National Center for Biotechnology Information, U.S. National Library of Medicine, National Institutes of Health (NIH), U.S. Department of Health & Human Services, and, which sources content from all federal, state, local, tribal, and territorial government publication portals (.gov, .mil, .edu). Funding for and content contributors is made possible from the U.S. Congress, E-Government Act of 2002.
Crowd sourced content that is contributed to World Heritage Encyclopedia is peer reviewed and edited by our editorial staff to ensure quality scholarly research articles.
By using this site, you agree to the Terms of Use and Privacy Policy. World Heritage Encyclopedia™ is a registered trademark of the World Public Library Association, a non-profit organization.

Copyright © World Library Foundation. All rights reserved. eBooks from World eBook Library are sponsored by the World Library Foundation,
a 501c(4) Member's Support Non-Profit Organization, and is NOT affiliated with any governmental agency or department.