MERGE
TDV uses SQL 2003/2008 MERGE syntax. TDV pushes MERGE if the data source supports it.
Federated merge is possible if the target table's database supports positioned updates, inserts and deletes in its JDBC driver.
MERGE and Data Sources
The following table lists data sources and their treatment when MERGE is involved.
Data Source | Comments |
DB2 Versions 8 | Supports ANSI MERGE 2003/2008. MERGE is pushed whenever possible. However, in the non-push (federated) case, the driver does not support some of the features required for full support. If the MERGE statement contains a WHEN NOT MATCHED THEN INSERT clause, the MERGE statement may fail. Newer versions of DB2 do not have this problem. The workaround is to change the MERGE statement so that it is completely pushed to DB2. |
DB2 Versions 9.5, 10.5, and z/OS | |
MySQL | Does not support MERGE. However, it does have REPLACE INTO and DUPLICATE KEY. For a TDV MERGE of MySQL data to succeed, the MySQL target table must have a primary key, and all columns in the primary key must be part of the MERGE. For a MERGE on tables from the same MySQL connection: if one ResultSet is modified, the driver closes the other ResultSet. The workaround is to create a copy of the data source so that you are using two different JDBC connections to the same data source. |
Netezza | Not possible to do a MERGE, because Netezza does not support updatable cursors. |
Oracle | |
SQL Server 2008, 2012 | |
Sybase ASE | Version 15.7 is the first version of ASE to support MERGE. |
Sybase IQ | Versions up to and including 16 do NOT support MERGE. The JTDS driver for Sybase supports scrolling updatable result sets; the JConnect 7 driver does not. |
Teradata | Teradata 12 and 13 support SQL 2003 MERGE. Teradata 14 supports DELETE, but does not support search conditions in the WHEN clause. Federated MERGE may be possible under either of the following conditions: • The target table contains a column that is the only member of a unique index. • A column is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set. |
| Does not support federated MERGE because its driver does not support scrollable cursors. |
6.x | Supports ANSI SQL 2003 MERGE. |
MERGE Examples
This section includes a number of representative MERGE examples.
Example
This example tests the subquery IN clause.
PROC ( : !DSMAP)
PROCEDURE m_mixed(out x CURSOR)
BEGIN
DECLARE guid VARCHAR(10) DEFAULT SUBSTRING('${ITEM_GUID}', 1, 10);
DELETE FROM /users/composite/test/sources/oracle/DEV1/UPDATES ;
INSERT INTO /users/composite/test/sources/oracle/DEV1/UPDATES (col_id,col_decimal, col_varchar) VALUES(3,30,guid),(4,40,guid),(5,50,guid),(6,60,guid),(-1,-10,guid);
MERGE INTO /users/composite/test/sources/oracle/DEV1/UPDATES
USING (SELECT * FROM /shared/examples/ds_inventory/tutorial/inventorytransactions) inventorytransactions
ON col_id = unitsreceived
WHEN MATCHED AND guid = col_varchar and col_decimal IN (SELECT o10_id * 10 FROM /users/composite/test/sources/oracle/DEV1/O10 WHERE o10_id IN (3,4)) THEN DELETE;
OPEN x FOR SELECT col_id,col_char,col_tinyint,col_smallint,col_decimal FROM /users/composite/test/sources/oracle/DEV1/UPDATES WHERE guid = col_varchar;
END
Example
This example tests Microsoft SQL Server.
PROC (SERIAL)
PROCEDURE m_pushed(out x CURSOR)
BEGIN
DECLARE guid VARCHAR(10) DEFAULT SUBSTRING('${ITEM_GUID}', 1, 6) || '019';
DELETE FROM /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/updates WHERE guid = c_varchar;
INSERT INTO /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/updates (c_id, c_decimal, c_varchar) values(3, null, guid), (4, 40, guid);
MERGE INTO /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/updates
USING /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/s10
ON c_id = S_id AND c_varchar = guid
WHEN MATCHED AND c_decimal + 1 IS NOT NULL THEN UPDATE SET c_id = S_id +10000 + c_id * 1000, c_char=S_char
;
OPEN x FOR SELECT c_id, c_decimal, c_char FROM /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/updates WHERE c_varchar = guid;
END
Example
This example tests DB2.
PROC (DISABLED)
PROCEDURE m_mixed(out x CURSOR)
BEGIN
DELETE FROM /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES;
INSERT INTO /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES (c_id, c_decimal, c_varchar) values(3, null, '${ITEM_GUID}'), (4, 40, '${ITEM_GUID}');
MERGE INTO /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES
USING /users/composite/test/sources/mssql_2k8/devstd/devstd/dbo/s10
ON c_id = S_id and c_varchar = '${ITEM_GUID}'
WHEN NOT MATCHED THEN INSERT (c_id,c_char, c_varchar) VALUES (s_int, 'hey' || S_money, '${ITEM_GUID}');
OPEN x FOR SELECT c_id, c_char FROM /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES WHERE c_varchar = '${ITEM_GUID}';
END
Example
In a MERGE statement, the same row of a table cannot be the target for combinations of UPDATE, DELETE and INSERT operations. This happens when a target row matches more than one source row. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
PROC
PROCEDURE m_pushed(out x CURSOR)
BEGIN
DECLARE guid VARCHAR(10) DEFAULT SUBSTRING('${ITEM_GUID}', 1, 10);
DELETE FROM /users/composite/test/sources/oracle/DEV1/UPDATES ;
INSERT INTO /users/composite/test/sources/oracle/DEV1/UPDATES (col_id,col_decimal, col_varchar) VALUES(3,30, guid);
MERGE INTO /users/composite/test/sources/oracle/DEV1/UPDATES
USING (SELECT * FROM /shared/examples/ds_inventory/tutorial/inventorytransactions) inventorytransactions
ON col_id = purchaseorderid
WHEN MATCHED AND col_varchar = guid THEN UPDATE SET col_tinyint=productid;
END
Example
This example tests that DB2 does not allow a row to be deleted twice.
PROC
PROCEDURE m_error(out x CURSOR)
BEGIN
DECLARE guid VARCHAR(10) DEFAULT SUBSTRING('${ITEM_GUID}', 1, 10);
DELETE FROM /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES;
INSERT INTO /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES (c_id, c_decimal, c_varchar) values(1, null, guid);
MERGE INTO /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES
USING (SELECT case WHEN "mixedCaseCol" in (1,2) THEN 1 ELSE "mixedCaseCol" end "mixedCaseCol"FROM /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/mixedCaseSchema/mixedCaseTable) mixedCaseTable
ON c_id = mixedCaseCol
WHEN MATCHED AND c_varchar = guid THEN DELETE
WHEN NOT MATCHED THEN INSERT (c_id, c_varchar, c_decimal) VALUES (3, guid, 50);
OPEN x FOR SELECT c_id, c_decimal FROM /users/composite/test/sources/"db2_9.5"/qa1_dev100_designbyexample/QA1/UPDATES WHERE guid = c_varchar;
END
Example
This test is a NULL scan. Nothing should be executed.
PROC
PROCEDURE m_nullscan()
BEGIN
MERGE INTO /users/composite/test/sources/oracle/DEV1/UPDATES
USING /shared/examples/ds_inventory/tutorial/inventorytransactions
ON 1<>1
WHEN MATCHED THEN DELETE
;
END
Example
In this test, the left side of the JOIN is a physical selection.
PROC
PROCEDURE m_mixed_physical_selection()
BEGIN
MERGE
INTO /users/composite/test/sources/oracle/DEV1/UPDATES
USING /shared/examples/ds_inventory/tutorial/inventorytransactions
ON col_id = purchaseorderid AND col_char = pri_mp(781598358)
WHEN MATCHED THEN UPDATE SET col_tinyint=productid;
MERGE {option disable_push}
INTO /users/composite/test/sources/oracle/DEV1/UPDATES
USING /shared/examples/ds_inventory/tutorial/inventorytransactions
ON col_id = purchaseorderid AND col_char = pri_mp(781598358)
WHEN MATCHED THEN UPDATE SET col_tinyint=productid;
END
Example
This test verifies that MySQL requires the target table to have a unique index for all columns to be selected in that index.
PROC
PROCEDURE m_mixed()
BEGIN
MERGE INTO /users/composite/test/sources/mysql_v5/inventory/products
USING /users/composite/test/sources/mysql_v5/inventory/inventorytransactions
ON productname = transactiondescription
WHEN MATCHED THEN UPDATE SET categoryid = categoryid
;
END
Example
If the following SQL had used a SELECT statement, the logical plan generator would probably prune the left side. Using a MERGE prevents this from happening.
PROC
PROCEDURE m_outer_join_pruner()
BEGIN
MERGE
/users/composite/test/sources/mysql_v5/covoter/district USING
/users/composite/test/sources/mysql_v5/mysql/m10
ON
m10.m_id = district.oid
WHEN MATCHED THEN DELETE;
END
Example
The following MERGE is actually a no-op scan. No rows are matched, and there is no WHEN NOT MATCHED clause. The query engine should replace it with a no-op scan operator.
PROC
PROCEDURE null_scan()
BEGIN
MERGE INTO /users/composite/test/sources/oracle/DEV1/UPDATES u
USING /shared/examples/ds_inventory/tutorial/products p
ON 1 = 2
WHEN MATCHED THEN DELETE
;
END