Restrictions on Source and Target Tables

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 HCL OneDB™ instance, or it can be a remote table that is managed by a different HCL OneDB 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.

Restrictions on the Target Table

The target table object must be in a database of the same HCL OneDB 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.

Restriction on the combined row length

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);

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; 

Restrictions on distributed MERGE statements

Distributed MERGE transactions cannot access the database of another HCL OneDB 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 HCL OneDB 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).

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 HCL OneDB supports in cross-server DML operations, see Data Types in Cross-Server Transactions.