Handling Duplicate Rows

While MERGE is executing, the same row in the target table cannot be updated or deleted more than once. No attempt is made to update or delete any row in the target that did not already exist before the MERGE statement was executed. That is, there are no updates or deletes of rows that the same MERGE statement inserted into the target.

The following example of the MERGE statement uses the transaction table new_sale as the source table from which to insert or update rows in the fact table sale. The join condition in this example tests whether the new_sale.cust_id column value matches the sale.cust_id column value. 

MERGE INTO sale USING new_sale AS n 
   ON sale.cust_id = n.cust_id
   WHEN MATCHED THEN UPDATE 
      SET sale.salecount = sale.salecount + n.salecount
   WHEN NOT MATCHED THEN INSERT (cust_id, salecount) 
      VALUES (n.cust_id, n.salecount);
To execute this MERGE statement, the database server joins the target and source tables, and then applies the specified equality condition to process the result of the join:
  • For rows that satisfy the condition (because the sale.cust_id value matches the new_sale.cust_id value), MERGE updates the sale.salecount column value, according to the SET clause specification.
  • For rows that do not satisfy the condition (because no row in the sale table has the same cust_id value as new_sale.cust_id), MERGE inserts new rows containing the new_sale.cust_id and new_sale.salecount values into the sale table, according to the VALUES clause specification.
For the MERGE statement in the previous example, suppose that the sale target table contains the two records and that the new_sale source table contains the three records.
Table 1. Records in 'sale' Table
cust_id sale_count
Tom 129
Julie 230
Table 2. Records in 'new_sale' Table
cust_id sale_count
Tom 20
Julie 3
Julie 10

When merging new_sale into sale by specifying the expression sale.cust_id = new_sale.cust_id as the matching condition, the MERGE statement returns an error, because it attempts to update one of the records in the sale target table more than once.

Data Types in Distributed MERGE Operations

If the source table or view (or any table object referenced in the source query) specifies a table object in a database of a Informix® instance other than the local instance that manages the database of the target table, the MERGE statement can access columns of only the following data types in the remote database:
  • Built-in data types that are not opaque
  • BOOLEAN
  • LVARCHAR
  • DISTINCT of the built-in data types that are not opaque
  • DISTINCT of BOOLEAN
  • DISTINCT of LVARCHAR
  • DISTINCT of any DISTINCT data type that appears in this list.

Cross-server distributed MERGE operations can support these DISTINCT types only if the DISTINCT types are cast explicitly to built-in types, and all of the DISTINCT types, their data type hierarchies, and their casts are defined exactly the same way in each participating database. For additional information about the data types that Informix® supports in cross-server DML operations, see Data Types in Cross-Server Transactions.

MERGE operations cannot access a database of another Informix® instance unless both server instances support either a TCP/IP or an IPCSTR connection, as defined in their DBSERVERNAME or DBSERVERALIASES configuration parameters and in the sqlhosts file or SQLHOSTS registry subkey. This connection-type requirement applies to any communication between Informix® instances, even if both database servers reside on the same computer.

Distributed MERGE operations that access table objects in other databases of the local Informix® instance, however, can access all of the cross-server data types in the preceding list, and these additional data types:
  • Most built-in opaque data types, as listed in Data Types in Cross-Database Transactions
  • DISTINCT of the same built-in opaque types
  • DISTINCT of any of the data types in either of the two preceding lines
  • Opaque user-defined data types (UDTs) that are explicitly cast to built-in data types.

The MERGE statement also supports Distributed Relational Database Architecture™ (DRDA®) protocols in common client APIs. For the Informix® data types that MERGE can return from a remote database through DRDA® protocols, see the Informix® Administrator's Guide for lists of the Informix® data types that are supported (and that are not supported) by DRDA®.