A
relational database management system
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
uses
SQL (also called ''upsert'') statements to
INSERT
new records or
UPDATE
or
DELETE
existing records depending on whether
condition matches. It was officially introduced in the
SQL:2003 standard, and expanded in the
SQL: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
PostgreSQL,
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a proprietary multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for ru ...
,
IBM Db2,
Teradata,
EXASOL,
Firebird,
CUBRID,
H2,
HSQLDB,
MS SQL,
MonetDB,
Vectorwise
Actian Vector (formerly known as VectorWise) is an SQL relational database management system designed for high performance in analytical database applications.
It published record breaking results on the Transaction Processing Performance Council ...
and
Apache Derby support the standard syntax. Some also add non-standard SQL extensions.
Synonymous
Some database implementations adopted the term ''upsert'' (a
portmanteau
In linguistics, a blend—also known as a blend word, lexical blend, or portmanteau—is a word formed by combining the meanings, and parts of the sounds, of two or more words together. of ''update'' and ''insert'') to a
database
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and a ...
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+) and
SQLite (v3.24+). It is also used to abbreviate the "MERGE" equivalent pseudo-code.
It is used in
Microsoft Azure SQL Database.
Other non-standard implementations
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.
MySQL
MySQL () is an Open-source software, open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A rel ...
, for example, supports the use of syntax 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 syntax, which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an clause for the statement, 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 works similarly. It also supports as an alias for compatibility with MySQL.
Firebird supports though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version, , 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 and clauses, distinguishing them with
guards.
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
extends with supporting guards and also with supporting Left Join via clauses.
PostgreSQL supports merge since version 15 but previously supported merging via .
CUBRID supports statement. And supports the use of syntax. It also supports for compatibility with MySQL.
Apache Phoenix supports and
UPSERT SELECT
syntax.
Spark SQL supports and clauses in actions.
Apache Impala supports .
Usage in NoSQL
A similar concept is applied in some
NoSQL
NoSQL (originally meaning "Not only SQL" or "non-relational") refers to a type of database design that stores and retrieves data differently from the traditional table-based structure of relational databases. Unlike relational databases, which ...
databases.
In
MongoDB the fields in a value associated with a key can be updated with an operation. The raises an error if the key is not found. In the operation it is possible to set the 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 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 operation has always a ''set or replace'' semantics.
See also
* Join in particular:
**
Join (SQL)
A join clause in the Structured Query Language (SQL) combines column (database), columns from one or more table (database), tables into a new table. The operation corresponds to a Join (relational algebra), join operation in relational algebra. In ...
**
join (Unix)
References
*
*
External links
Oracle 11g Release 2 documentationon
on
Microsoft SQL Server documentation
{{SQL
SQL keywords
Articles with example SQL code