Usage and Functionality of MERGE statement
– MERGE statement is used to update or insert data into a table based on specified conditions.
– It combines the functionality of UPDATE and INSERT statements.
– The syntax includes the target table, source table, and the condition for matching records.
– When the condition is met, the statement performs an update on the target table.
– When the condition is not met, the statement performs an insert into the target table.
Implementations of MERGE statement
– PostgreSQL, Oracle Database, IBM Db2, Teradata, EXASOL, Firebird, CUBRID, H2, HSQLDB, MS SQL, Vectorwise, and Apache Derby support the standard syntax.
– Some databases have non-standard SQL extensions for the MERGE statement.
– Non-standard implementations include MySQL, SQLite, Firebird, IBM Db2, PostgreSQL, CUBRID, Apache Phoenix, Spark SQL, and Apache Impala.
– These implementations may have additional features or variations in syntax.
Synonymous terms and abbreviations
– Upsert is a term used to describe a database statement that combines update and insert operations.
– PostgreSQL and SQLite use the term Upsert for their MERGE equivalent statement.
– Upsert is also used in Microsoft Azure SQL Database.
– It can be used to insert a record if it doesn’t exist or update an existing record.
– Upsert is sometimes used as an abbreviation for the MERGE statement.
Other non-standard implementations
– MySQL supports the use of ‘INSERT … ON DUPLICATE KEY UPDATE’ syntax for similar functionality.
– MySQL also supports ‘REPLACE INTO’ syntax, which attempts an insert and deletes the existing row if it exists.
– SQLites ‘INSERT OR REPLACE INTO’ works similarly to MySQL’s syntax.
– Firebird supports MERGE INTO statement but doesn’t throw an error for multiple source data rows.
– IBM Db2 extends the syntax with multiple ‘WHEN MATCHED’ and ‘WHEN NOT MATCHED’ clauses.
– Microsoft SQL Server supports guards and left join in the MERGE statement.
Usage in NoSQL
– Some NoSQL databases have similar concepts to the MERGE statement.
– MongoDB allows updating fields associated with a key using the update operation.
– Redis uses the SET operation to set the value associated with a key.
– MongoDB’s update operation raises an error if the key is not found.
– Redis does not know the internal structure of the value, so an update has no meaning.
– Both MongoDB and Redis have mechanisms to handle insert or replace semantics.
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 SQL:2003 standard, and expanded[citation needed] in the SQL:2008 standard.
