Merge (SQL)
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.