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.
	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