Merge (SQL)
Encyclopedia
A relational database management system
uses SQL
) statements to
matches. It was officially introduced in the SQL:2008 standard.
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
Right join is employed over Target and Source tables. That is, rows present in Source and missed from Target do run the action, the rows missed from Source and present in Target are ignored.
If multiple Source rows match a given Target row, error is mandated by SQL-2003.
, DB2
, and MS SQL support the standard syntax. Some also add non-standard SQL extensions.
MySQL
, for example, supports the use of
SQLite
's
Firebird
supports
IBM DB2
extends syntax with multiple
.
Microsoft SQL extends with supporting guards and also with supporting Left Join via
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
uses SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
MERGE (upsertUpsert
The term "Upsert" refers to any 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...
) statements to
INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
new records or UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
existing records depending on whether or not a conditionCondition (SQL)
A relational database management system uses SQL conditions or expressions in WHERE clauses and in HAVING clauses to SELECT subsets of data.- Types of condition :...
matches. It was officially introduced in the SQL:2008 standard.
Usage
MERGE INTO table_name USING table_reference ON (condition)WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
Right join is employed over Target and Source tables. That is, rows present in Source and missed from Target do run the action, the rows missed from Source and present in Target are ignored.
If multiple Source rows match a given Target row, error is mandated by SQL-2003.
Implementations
Database management systems Oracle DatabaseOracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
, DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
, and MS SQL support the standard syntax. Some also add non-standard SQL extensions.
Other non-standard implementations
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.MySQL
MySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
, for example, supports the use of
INSERT ... ON DUPLICATE KEY UPDATE syntax which can be used to achieve the same effect. It also supports REPLACE INTO syntax, which first deletes the row, if exists, and then inserts the new one.SQLite
SQLite
SQLite is an ACID-compliant embedded relational database management system contained in a relatively small C programming library. The source code for SQLite is in the public domain and implements most of the SQL standard...
's
INSERT OR REPLACE INTO works similarly.Firebird
Firebird (database server)
Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....
supports
MERGE INTO though fails at throwing error when multiple Source data. Additionally 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 vs. update (e.g. setting a new sequence value only for new rows, not for existing ones.)IBM DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
extends syntax with multiple
WHEN MATCHED and WHEN NOT MATCHED clauses, distinguishing them with ... AND some-condition guardsGuard (computing)
In computer programming, a guard is a boolean expression that must evaluate to true if the program execution is to continue in the branch in question. The term is used at least in Haskell, Clean, Erlang, occam, Promela, OCaml and Scala programming languages. In Mathematica, guards are called...
.
Microsoft SQL extends with supporting guards and also with supporting Left Join via
WHEN NOT MATCHED BY SOURCE clauses.