Merge API uses MERGE INTO command defined by SQL:2003 standard with updates in SQL:2008. Additionally we support some non-standard extensions to this command. See specific database engine support information below. Later we plan to extend providers support by adding support for UPSERT-like commands.
MERGE INTO <target_table> [[AS] <alias>]
USING <source_data_set> [[AS] <alias>]
ON <match_condition>
-* one or both cases could be specified
WHEN MATCHED THEN <update_operation>
WHEN NOT MATCHED THEN <insert_operation>
<update_operation> := UPDATE SET <column> = <value> [, <column> = <value>]
<insert_operation> := INSERT (<column_list>) VALUES(<values_list>)
MATCH cases
It is possible to perform different operations for records, matched by ON match condition by specifying extra conditions on WHEN statement:
WHEN [NOT] MATCHED [AND <extra_condition>] THEN <match_specific_operation>
DELETE operation
DELETE operation could be used for WHEN MATCHED case.
WHEN MATCHED [AND <extra condition>] THEN DELETE
Not all data types supported or have limited support for some providers right now if you use client-side source. Usually it will be binary types. Check notes for specific provider below.
Microsoft SQL Server 2008+Microsoft SQL Server supports Merge API starting from SQL Server 2008 release. It supports all features from SQL:2008 standard and adds support for two new operations, not available for other providers:
Those two operations allow to update or delete target record when no matching record found in source. Of course it means that only target record available in context of those two operations.
Limitations:
Other notes:
Links:
IBM DB2Note: merge implementation was tested only on DB2 LUW.
DB2 supports all features from SQL:2008 standard.
Limitations:
Links:
FirebirdFirebird 2.1-2.5 supports all features from SQL:2003 standard. Firebird 3.0 supports all features from SQL:2008 standard.
Limitations:
Links:
Oracle DatabaseOracle supports SQL:2003 features and operation conditions from SQL:2008.
Instead of independent Delete operation it supports delete condition for Update operation, which will be applied only to updated records and work with updated values. To support this behavior, merge API supports Update Then Delete operation, that works only for Oracle. You also can use regular Update operation, but not Delete. For Delete operation you can use `UpdateWithDelete' with the same condition for update and delete.
Limitations:
Insert and second should be Update or UpdateWithDeleteDelete operation not supportedLinks:
Sybase/SAP ASEASE supports all features from SQL:2008 standard
Limitations:
Delete operations doesn't work (undocumented)MERGE is not allowed because different MERGE actions are referenced in the same WHEN [NOT] MATCHED clause", which is not true, because other commands with same set of operations just workOther notes:
Links:
IBM InformixInformix supports all features from SQL:2003 standard and Delete operation from SQL:2008.
Limitations:
Other notes:
null values if provider cannot infer them properlyLinks:
SAP HANA 2SAP HANA 2 supports all features from SQL:2003 standard.
Limitations:
Update operation must be first if both Update and Insert operations used in commandInsert operation not supportedLinks:
PostgreSQLPostgreSQL supports all features from SQL:2008 standard starting from version 15.
Limitations:
Links:
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4