Merge (SQL)

Add links
From English Wikipedia @ Freddythechick

A relational database management system uses SQL <syntaxhighlight lang="text" class="" style="" inline="1">MERGE</syntaxhighlight> (also called upsert) statements to INSERT new records or UPDATE 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

<syntaxhighlight lang="tsql"> 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 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 PostgreSQL,[1] Oracle Database, IBM Db2, Teradata, EXASOL, Firebird, CUBRID, H2, HSQLDB, MS SQL, Vectorwise and Apache Derby 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+)[2] and SQLite (v3.24+).[3] It is also used to abbreviate the "MERGE" equivalent pseudo-code.

It is used in Microsoft Azure SQL Database.[4]

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 <syntaxhighlight lang="mysql" class="" style="" inline="1">INSERT ... ON DUPLICATE KEY UPDATE</syntaxhighlight> syntax[5] 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 <syntaxhighlight lang="text" class="" style="" inline="1">>REPLACE INTO</syntaxhighlight> syntax,[6] which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an <syntaxhighlight lang="text" class="" style="" inline="1">IGNORE</syntaxhighlight> clause for the <syntaxhighlight lang="text" class="" style="" inline="1">INSERT</syntaxhighlight> statement,[7] 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 <syntaxhighlight lang="sql" class="" style="" inline="1">INSERT OR REPLACE INTO</syntaxhighlight> works similarly. It also supports <syntaxhighlight lang="text" class="" style="" inline="1">REPLACE INTO</syntaxhighlight> as an alias for compatibility with MySQL.[8]

Firebird supports <syntaxhighlight lang="text" class="" style="" inline="1">MERGE INTO</syntaxhighlight> though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version, <syntaxhighlight lang="sql" class="" style="" inline="1">UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)]</syntaxhighlight>, 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 <syntaxhighlight lang="text" class="" style="" inline="1">WHEN MATCHED</syntaxhighlight> and <syntaxhighlight lang="text" class="" style="" inline="1">WHEN NOT MATCHED</syntaxhighlight> clauses, distinguishing them with <syntaxhighlight lang="text" class="" style="" inline="1">... AND some-condition</syntaxhighlight> guards.

Microsoft SQL Server extends with supporting guards and also with supporting Left Join via <syntaxhighlight lang="tsql" class="" style="" inline="1">WHEN NOT MATCHED BY SOURCE</syntaxhighlight> clauses.

PostgreSQL supports merge since version 15 but previously supported merging via <syntaxhighlight lang="postgresql" class="" style="" inline="1">INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action</syntaxhighlight>.[9]

CUBRID supports <syntaxhighlight lang="text" class="" style="" inline="1">MERGE INTO</syntaxhighlight>[10] statement. And supports the use of <syntaxhighlight lang="mysql" class="" style="" inline="1">INSERT ... ON DUPLICATE KEY UPDATE</syntaxhighlight> syntax.[11] It also supports <syntaxhighlight lang="text" class="" style="" inline="1">REPLACE INTO</syntaxhighlight> for compatibility with MySQL.[12]

Apache Phoenix supports <syntaxhighlight lang="text" class="" style="" inline="1">UPSERT VALUES</syntaxhighlight>[13] and UPSERT SELECT[14] syntax.

Spark SQL supports <syntaxhighlight lang="text" class="" style="" inline="1">UPDATE SET *</syntaxhighlight> and <syntaxhighlight lang="text" class="" style="" inline="1">INSERT *</syntaxhighlight> clauses in actions.[15]

Apache Impala supports <syntaxhighlight lang="text" class="" style="" inline="1">UPSERT INTO ... SELECT</syntaxhighlight>.[16]

Usage in NoSQL

A similar concept is applied in some NoSQL databases.

E.g. in MongoDB the fields in a value associated with a key can be updated with an <syntaxhighlight lang="text" class="" style="" inline="1">update</syntaxhighlight> operation. The <syntaxhighlight lang="text" class="" style="" inline="1">update</syntaxhighlight> raises an error if the key is not found. In the <syntaxhighlight lang="text" class="" style="" inline="1">update</syntaxhighlight> operation it is possible to set the <syntaxhighlight lang="text" class="" style="" inline="1">upsert</syntaxhighlight> 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 <syntaxhighlight lang="text" class="" style="" inline="1">SET</syntaxhighlight> 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 <syntaxhighlight lang="text" class="" style="" inline="1">SET</syntaxhighlight> operation has always a set or replace semantics.

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. ^ "PostgreSQL Upsert Using INSERT ON CONFLICT statement". PostgreSQL Tutorial. Archived from the original on Nov 28, 2022.
  3. ^ "upsert", SQLite, visited 6-6-2018.
  4. ^ "MERGE (Transact-SQL)". Transact-SQL Reference (Database Engine). Microsoft Learn. Archived from the original on Jun 24, 2016.
  5. ^ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
  6. ^ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
  7. ^ "MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax". Retrieved 29 October 2013.
  8. ^ "SQL As Understood By SQLite: INSERT". Retrieved 2012-09-27.
  9. ^ PostgreSQL INSERT page
  10. ^ "New CUBRID 9.0.0". CUBRID Official Blog. 2012-10-30. Retrieved 2012-11-08.
  11. ^ CUBRID :: Data Manipulation Statements :: Insert :: ON DUPLICATE KEY UPDATE Clause
  12. ^ CUBRID :: Data Manipulation Statements :: Replace
  13. ^ "UPSERT VALUES".
  14. ^ "UPSERT SELECT".
  15. ^ "MERGE INTO (Delta Lake on Databricks)".
  16. ^ "UPSERT Statement (Apache Impala Documentation)".

External links

Retrieved from "https://enwiki.freddythechick.net/index.php?title=Merge_(SQL)&oldid=206188"