Restrictions on Source and Target Tables

Which table objects can be the source or target of the MERGE statement depends on attributes of the table object, and on what access privileges are held by the user who issues the MERGE statement.

The target table must be local to the database to which the current session is connected, but you can specify a remote a table as the source table, or in subqueries of the SET clause for UPDATE operations, and in subqueries of the VALUES clause for INSERT operations.

Sections that follow identify additional restrictions on the source and target tables.

Restrictions on the Source Table

The source object can be the name or synonym of a STANDARD, RAW, TEMP, EXTERNAL, or collection-derived table, or a view. It can be in the same database as the target object, or in a different database of the local Informix® instance, or it can be a remote table that is managed by a different Informix® instance.

If the source is a collection-derived table that is defined by the result of a query, the USING clause can declare names for derived columns that the SET and VALUES clauses of the MERGE statement can reference.

The user who issues the MERGE statement must hold the Connect access privilege (or a higher privilege) on the database of the source object, and must also hold the Select privilege (or a higher privilege) on the source object. The user can be granted these access privileges individually, or can hold them as a member of the PUBLIC group, or through the current or default role of the user, if the role or PUBLIC holds those privileges.

If the source object or any of its columns is protected by a label-based security policy, the user who issues the MERGE statement must have a security label (or must hold a security policy exemption) that provides sufficient credentials to read the source object. If the credentials of the user are insufficient to read protected columns, according to the standard label-based access control (LBAC) rules, then the MERGE statement can process only a subset of the source data. If this subset is empty, the MERGE statement cannot insert any values from the source object into the target table.

The following restrictions apply to the source table object:

The source cannot be a view on which an enabled SELECT trigger is defined.
The source cannot be a typed table in the same table hierarchy as the target table.
In a Delete merge, the source cannot have a child-table relationship with the target, as defined by an enabled referential constraint, if that constraint was defined with the ON DELETE CASCADE keywords. (Child-table relationships have no effect on the Delete merge, however, unless a target table constraint specifies cascading deletes.)

Restrictions on the Target Table

The target table object must be in a database of the same Informix® instance to which the current session is connected. It can be the name or synonym of a STANDARD, RAW, or TEMP table, or an updatable view. If the target is a supertable within a table hierarchy, the Delete clause also deletes the corresponding rows in all the subtables of the target table.

The user who issues the MERGE statement must hold the Connect access privilege (or a higher privilege) on the database of the target object, and must also hold the Insert privilege and the Update or Delete privilege on the target object, if the MERGE statement includes the corresponding Insert, Update, or Delete clause.

The following restrictions apply to the target table of the MERGE statement. If that table has any of the following attributes, the MERGE operation returns an error.

The target cannot be a typed table in the same table hierarchy as the source table.
The target cannot be a Virtual Table Interface (VTI) table.
The target cannot be an object that the CREATE EXTERNAL TABLE statement defined.
The target cannot be in a database of a remote Informix® instance.
The target cannot be a system catalog table.
The target cannot be a view on which an enabled INSTEAD OF trigger is defined.
The target cannot be a read-only view.
The target cannot be a pseudo-table (a memory-resident object in a system database, such as the sysmaster or sysadmin databases).
The target cannot be a data source of any subquery of the same MERGE statement, including subqueries in the ON clause, in the SET clause, or in the VALUES clause.
If the MERGE statement includes the DELETE clause, the target cannot have a parent-table relationship with the source table, if this relationship is defined by an enabled referential constraint that specifies the ON DELETE CASCADE keywords.

Restriction on the combined row length

The source table and the target table in the MERGE statement cannot have a total combined row length ( = row size of source table + row size of target table ) greater than 32,767 bytes. Otherwise, the MERGE statement fails with an error, as in the following example:

CREATE TABLE t1 
       (f1  INT, 
        f2  VARCHAR(10),
        lv1 LVARCHAR(5000),
        lv2 LVARCHAR(4000),
        lv3 LVARCHAR(8000));
CREATE TABLE t2 
       (f1  INT, 
        f2  VARCHAR(10),
        lv1 LVARCHAR(5000),
        lv2 LVARCHAR(4000),
        lv3 LVARCHAR(8000));

INSERT INTO t1 (f1,f2) VALUES (1,'t1 1');
INSERT INTO t1 (f1,f2) VALUES (2,'t1 2');
INSERT INTO t1 (f1,f2) VALUES (3,'t1 3');
INSERT INTO t1 (f1,f2,lv1) VALUES (7,'t1 7',
   'loooooooooooooooooong');

INSERT INTO t2 (f1,f2) VALUES (3,'t2 3');
INSERT INTO t2 (f1,f2) VALUES (4,'t2 4');
INSERT INTO t2 (f1,f2) VALUES (5,'t2 5');
INSERT INTO t2 (f1,f2) VALUES (6,'t2 6');

MERGE INTO t2 AS o  USING t1 AS n ON o.f1 = n.f1 
   WHEN NOT MATCHED THEN INSERT ( o.f1,o.f2)
      VALUES ( n.f1,n.f2);

The MERGE statement above fails, because the sum of the row lengths of the source and target tables exceeds the upper limit of 32,767 bytes.

For MERGE operations that include only the INSERT clause (but no DELETE clause nor UPDATE clause), you can circumvent this row length limit by replacing the MERGE statement with INSERT INTO . . . SELECT statements. For the same tables and data values in the MERGE example above, the following INSERT statements run successfully:

INSERT INTO t2(f1, f2) 
   SELECT t1.f1, t1.f2 FROM t1 
      WHERE NOT EXISTS 
         (SELECT f1, f2 FROM t2 
            WHERE t2.f1 = t1.f1);

INSERT INTO t2(f1,f2) 
   SELECT t1.f1, t1.f2 FROM t1 
      LEFT JOIN t2 ON t1.f1 = t2.f1
         WHERE t2.f1 IS NULL;

After the two INSERT INTO . . . SELECT operations, table t2 contains what the row size restriction prevented the previous MERGE example from returning.

Restrictions on distributed MERGE statements

If the source and target tables are not in the same database, both databases must satisfy the compatibility requirements for cross-database and cross-server DML operations:

Both databases must be of release versions that support all the data types in the source table and in the target table.
If one database is ANSI-compliant, the other must also be ANSI-compliant.
If one database is not ANSI-compliant but uses explicit transaction logging, the other must also support explicit transaction logging. (In this case, their buffered or unbuffered logging modes need not match.)
If one database does not support transaction logging, the other also must not.
Both databases must have the same NLSCASE sensitivity setting.

A distributed MERGE statement cannot, for example, specify a source table in a case-sensitive database and a target table in a database created as NLSCASE INSENSITIVE, whether or not either table includes NCHAR or NVARCHAR columns.

Distributed MERGE transactions cannot access the database of another Informix® instance unless both servers define TCP/IP or IPCSTR connections in their DBSERVERNAME or DBSERVERALIASES configuration parameters and in the sqlhosts file or SQLHOSTS registry subkey. The requirement, that both participating servers support the same type of connection (either TCP/IP or else IPCSTR), applies to any communication between Informix® instances, even if both reside on the same computer.

Data types valid in distributed MERGE statements

When the source table and the target tables are in the same database, MERGE statements can access all the data-type categories that the Data Type topics describe, including BYTE and TEXT objects, OPAQUE and DISTINCT types, complex types, and user-defined types (UDTs).

A MERGE statement whose source table is in a database of another Informix® server instance, however, is subject to the restrictions on column data types in other distributed data manipulation language (DML) operations. A cross-server MERGE statement can reference columns with only the following subset of data types:

Built-in data types that are not opaque or complex
BOOLEAN
BSON
JSON
LVARCHAR
DISTINCT of built-in types that are not opaque
DISTINCT of BOOLEAN
DISTINCT of BSON
DISTINCT of JSON
DISTINCT of LVARCHAR
DISTINCT of the DISTINCT types 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 the source database and in the target database.

Cross-server operations can also return the IDSSECURITYLABEL data type from a table protected by row-level LBAC security, if the user issuing the MERGE statement holds sufficient LBAC credentials. Accessing a table protected only by column-level LBAC security requires similar credentials, but in that case the table has no IDSSECURITYLABEL column.

Cross-server DML operations cannot reference a column or expression of a complex, large-object, nor user-defined data type (UDT), nor of an unsupported DISTINCT or built-in opaque type. For additional information about the data types that Informix® supports in cross-server DML operations, see Data Types in Cross-Server Transactions.

Distributed MERGE operations in which the source table and the target table are in a different databases of the same Informix® server instance can access the same data types that are listed above for cross-server MERGE statements. They can also access the following additional data types that are not supported in cross-server MERGE operations:

Most of the built-in opaque data types, as listed in Data Types in Cross-Database Transactions
DISTINCT of the built-in types that are referenced in the line above
DISTINCT of any of the data types that are listed in either of the two lines above
Opaque UDTs that can be cast explicitly to built-in data types.

Cross-database INSERT operations can support these DISTINCT and opaque UDTs only if all the opaque UDTs and DISTINCT types are cast explicitly to built-in types, and all of the opaque UDTs, DISTINCT types, data type hierarchies, and casts are defined exactly the same way in each participating database.

If the target and source tables are in different databases, whether of the same server instance or of different server instances, the MERGE statement fails if it references a table, view, or synonym that includes a column of any of the following opaque or complex data types:

IMPEXP, IMPEXPBIN, LOLIST, or SENDRECV built-in opaque type
DISTINCT of any of those opaque data types
Any complex type, including COLLECTION, LIST, MULTISET, or SET, and named or unnamed ROW types.