请输入您要查询的百科知识：

　

词条 Merge (SQL)
释义

  1. Usage

  2. Implementations

      {{anchor|upsert}} Synonymous   Other non-standard implementations 

  3. See also

  4. References

  5. External links

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the 2003 standard, and expanded in the 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 the ON field(s) in the Source matches the ON field(s) in the Target, then UPDATE
  • If the ON field(s) in the Source does not match the ON field(s) in the Target, then INSERT
  • If the ON field(s) does not exist in the Source but does exist in the Target, then no action is performed.
  • If the 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 MERGE statement

Implementations

Database management systems Oracle Database, DB2, Teradata, EXASOL, Firebird, CUBRID, HSQLDB, MS SQL, 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+)[1] and SQLite (v3.24+).[2] It is also used to abbreviate the "MERGE" equivalent pseudo-code.

It is used in Microsoft SQL Azure.[3]

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[4] 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,[5] 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,[6] 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 REPLACE INTO as an alias for compatibility with MySQL.[7]

Firebird supports 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 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 {{code|lang=tsql|WHEN NOT MATCHED BY SOURCE}} clauses.

PostgreSQL supports merging via {{code|2=sql|INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action}}.[8]CUBRID supports MERGE INTO[9] statement. And supports the use of {{code|lang=mysql|INSERT ... ON DUPLICATE KEY UPDATE}} syntax.[10] It also supports REPLACE INTO for compatibility with MySQL.[11]

Apache Phoenix supports UPSERT VALUES[12] and UPSERT SELECT[13] syntax.

See also

  • Join in particular:
    • Join (SQL)
    • join (Unix)

References

1. ^PostgreSQL-tutorial
2. ^upsert sqlite.org visited 6-6-2018
3. ^[https://msdn.microsoft.com/en-us/library/bb510625.aspx Transact-SQL Reference (Database Engine): MERGE (Transact-SQL)]
4. ^MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
5. ^MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
6. ^{{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}}
7. ^{{cite web|url=http://www.sqlite.org/lang_insert.html|title=SQL As Understood By SQLite: INSERT|accessdate=2012-09-27}}
8. ^PostgreSQL INSERT page
9. ^{{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}}
10. ^CUBRID :: Data Manipulation Statements :: Insert :: ON DUPLICATE KEY UPDATE Clause
11. ^CUBRID :: Data Manipulation Statements :: Replace
12. ^{{cite web|url=https://phoenix.apache.org/language/#upsert_values|title=UPSERT VALUES}}
13. ^{{cite web|url=https://phoenix.apache.org/language/#upsert_select|title=UPSERT SELECT}}
  • {{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= The Definitive Guide |publisher= O'Reilly |date=September 2010 |isbn= 978-1-449-38156-1}}

External links

  • Oracle 11g Release 2 documentation on MERGE
  • Firebird 2.1 documentation on MERGE
  • DB2 v9 MERGE statement
  • Microsoft SQL Server documentation
  • HSQLdb 2.0 Data Change Statements
  • H2 (1.2) SQL Syntax page
{{SQL}}

2 : SQL keywords|Articles with example SQL code
随便看

　

开放百科全书收录14589846条英语、德语、日语等多语种百科知识，基本涵盖了大多数领域的百科知识，是一部内容自由、开放的电子版国际百科全书。

　

Copyright © 2023 OENC.NET All Rights Reserved
京ICP备2021023879号 更新时间：2024/11/12 6:14:13