MERGE statement

Use the MERGE statement to transfer data from a source table into a target table by combining UPDATE or DELETE operations with INSERT operations in a single SQL statement. You can also use this statement to join the source and target tables, and then perform only UPDATE operations, only DELETE operations, or only INSERT operations on the target table.

The MERGE statement supports the ANSI/ISO standard for SQL with Informix® extensions.

Syntax

(explicit id merg201a) merg201a (explicit id merg202a) merg202a (explicit id merg203a) merg203a MERGE [ directive ] INTO { target_table | target_view | target_synonym } [ [AS] alias ] USING { source_table | source_view | (source_subquery) } [ [AS] alias ] [ ( derived_column ) ] ONcondition { [] <Insert Clause> | [] { <Update Clause> | <Delete Clause> } }

Update Clause

WHEN MATCHED THEN UPDATE <SET Clause>[]

Delete Clause

WHEN MATCHED THEN DELETE

Insert Clause

WHEN NOT MATCHED THEN INSERT [ ( column ) ] <VALUES Clause>[]
Element Description Restrictions Syntax
alias A temporary name that you declare here for the target or source table object The source and target aliases must be different. If potentially ambiguous, the AS keyword must precede alias. Identifier
column A column in the target object into which to insert source data This must exist in the target object Identifier
condition A Boolean condition to apply to rows in the join of the source and target tables This can reference data values in the source and target objects Condition
derived_column A name that you declare here if the source object is a derived table The SET and VALUES clauses can reference this name. Identifier
directive A query optimizer directive The directive must be valid. Optimizer Directives
source_table, source_view, source_subquery A table (or the result of a query) containing data to be relocated Object must exist. See also Restrictions on Source and Target Tables. Database Object Name; SELECT statement
target_table, target_view, target_synonym The name or synonym of a table or updatable view in which to insert, update, or delete data See Restrictions on Source and Target Tables. Database Object Name

Usage

The MERGE statement of Informix® is a data manipulation language (DML) statement that joins a source table object with a target table or view. The condition that you specify after the ON keyword determines which rows from the source object are used in UPDATE or DELETE operations on the target, and which rows are used in INSERT operations on the target. The MERGE statement does not modify its source object.

The condition must be followed by the WHEN MATCHED THEN keywords of the Delete or the Update clause, or by the WHEN NOT MATCHED THEN keywords of the Insert clause, or by both the Update (or Delete) and Insert clauses.

  • If you specify both the Update clause and the Insert clause, the MERGE statement can perform both INSERT and UPDATE operations on the target object.
  • If you specify both the Delete clause and the Insert clause, the MERGE statement can perform both INSERT and DELETE operations on the target object.
  • If you specify no Insert clause, no INSERT operations are performed, but the Update clause must specify an UPDATE operation (or else the Delete clause must specify a DELETE operation) on the target object for source rows that match the condition.
  • If you specify no Update clause and no Delete clause, no UPDATE or DELETE operations are performed, but the Insert clause must specify an INSERT operation on the target object for source rows that do not match the condition.

The MERGE statement fails with an error if no Delete clause, no Update clause, and no Insert clause is specified.

The MERGE statement can have the following effects on the target object:
  • If the Update clause is included, the MERGE statement updates rows in the target table or view according to the specifications of the SET clause with data from rows in the source table for which the condition evaluates to true.
  • If the Delete clause is included, the MERGE statement deletes from the target table or view the rows for which the condition evaluates to true.
  • If the Insert clause is included, the MERGE statement inserts new rows into the target table or view according to the specifications of the VALUES clause with data from rows in the source table for which the condition evaluates to false.

A single MERGE statement, however, can have no more than two of these three effects, because the Delete clause and the Update clause are mutually exclusive.

For operations on large tables, make sure that these resources are available on your system:
  • A sufficient number of locks
  • Sufficient temporary dbspace storage for the intermediate join results
  • Sufficient dbspace storage for the results of the MERGE statement.

In a high-availability cluster configuration, you can issue the MERGE statement from a primary server or from an updatable secondary server.

Optimizer Directives and Subqueries

You can optionally specify one or more query optimizer directives after the MERGE keyword, such as access method directives, join order directives, and join method directives to specify how the source and target tables are joined. The goal-oriented directives like EXPLAIN and AVOID_EXECUTE are also valid in the MERGE statement.

Within the MERGE statement, subqueries can also include optimizer directives to control other aspects of the execution plan. Subqueries are valid in the following contexts in the MERGE statement:

  • In the condition of the ON clause
  • In the SET clause of the Update clause
  • In the VALUES clause of the Insert clause
  • In the USING clause if it specifies a source query, which can include a subquery in any context where the SELECT statement supports a subquery.

The MERGE statement fails with an error, however, if it includes a subquery that references the target table.

In a database that supports external directives, the query optimizer can also apply external directives to the outer join of the source and target tables, or to subqueries within the MERGE statement.

The ON Condition

The condition that follows the ON keyword specifies a join filter for the source and target table objects. This ON clause filter determines the matched rows and unmatched rows in the MERGE statement, based on the outer join of the target and source tables.
  • If the MERGE statement includes the Update clause, and the ON clause condition evaluates to true, then the corresponding rows are updated in the target.
  • If the MERGE statement includes the Delete clause, and the ON clause condition evaluates to true, then the corresponding rows are deleted from the target.
  • If the MERGE statement includes the Insert clause, and the ON clause condition evaluates to false, then the corresponding source rows are inserted into the target.

Update operations of the MERGE statement on rows that match the condition obey the UPDATE statement rules for the SET clause. For details of the syntax for specifying the updated values in the target table, see SET Clause.

Delete operations of MERGE on rows that match the condition obey the DELETE statement rules. For details of deleting values from the target table, see Using the WHERE Keyword to Specify a Condition.

Insert operations on rows that do not match the condition obey the INSERT statement rules for the VALUES clause. For details of the syntax for specifying the inserted values in the target table, see VALUES Clause.

Error Handling

If an error occurs while the MERGE statement is executing, the entire statement is rolled back.

For databases that support transaction logging, you can include error-handling logic that includes the ROLLBACK TO SAVEPOINT statement in a transaction that includes the MERGE statement and that defines one or more savepoints. After a partial rollback of the transaction to a savepoint, the effects of the INSERT, DELETE, or UPDATE operations of the MERGE statement persist in the target table if the MERGE statement precedes the savepoint in the lexical order of statements for that savepoint level of the transaction. The effects of MERGE are rolled back, however, if the MERGE statement follows the specified savepoint within the transaction.

In an ANSI-compliant database, data manipulation language (DML) statements are always in a transaction. These databases do not support the MERGE statement outside a transaction.

Constraint Checking

Enabled data-integrity constraints on the target object are enforced in MERGE operations.
  • If the checking mode is set to DEFERRED, the constraints are not checked until after the transaction is committed.
  • If the constraint-checking mode for the target table is set to IMMEDIATE, then unique and referential constraints are checked after all the UPDATE (or DELETE) and INSERT operations are complete. The NOT NULL and check constraints are checked during the UPDATE, DELETE, and INSERT operations.
For information on setting the constraint-checking mode, see the topic SET Transaction Mode statement.

If a referential constraint on the target table was defined with the ON DELETE CASCADE keywords, the DELETE clause of the MERGE statement also performs cascading deletes on rows of child tables of the target table.

A Delete merge fails, however, if an enabled referential constraint has established a parent-child relationship between the target and source tables, if the constraint was defined with the ON DELETE CASCADE keywords. The MERGE statement cannot perform cascading deletes on rows of its source table. For more information, see the topic Restrictions on DELETE When Tables Have Cascading Deletes.

If the START VIOLATIONS statement has defined an active violations table on the target table, then the MERGE statement can have the following effects on the target, violations, and diagnostic tables:
  • The conforming rows in the target table that match the join condition are either deleted or updated.
  • The target table also receives the conforming unmatched rows that MERGE successfully inserts.
  • The violation table receives the nonconforming rows.
  • A diagnostic table receives information about why the nonconforming rows failed to satisfy a constraint or a unique index during operations of the MERGE statement on the target table.

To enable a violations table and a diagnostic table on the target table, the SET Database Object Mode statement must set the constraints or unique indexes of the target table to ENABLED or FILTERING mode. For more information, see the topics Relationship to the SET Database Object Mode statement and SET Database Object Mode statement.

Using the MERGE Statement with Triggers

The target object can be a table on which an Update, Delete, or Insert trigger is defined. If both an Update trigger and an Insert trigger (or both a Delete trigger and an Insert trigger) are enabled on the target table, MERGE can act as the triggering event for both triggers, if the MERGE statement performs both UPDATE (or DELETE) and INSERT operations on the target.

If the MERGE statement includes operations that activate both Update (or Delete) and Insert triggers, the BEFORE trigger actions of both triggers are executed when the MERGE operation starts. Similarly, the AFTER trigger actions of both triggers are executed at the end of the MERGE operation. The FOR EACH ROW trigger actions are activated for each row processed.

Just as for any DML statement, the database server treats all the triggers that are activated by the same MERGE statement as a single trigger, and the resulting trigger action is the merged-action list. All the rules that govern a trigger action apply to the merged list as one list, and no distinction is made between the two original triggers. For more information, see Actions of Multiple Triggers.

The target object, however, cannot be a view on which an enabled INSTEAD OF trigger is defined. Before you can use that view as the target of a MERGE statement, you must disable or drop the INSTEAD OF trigger.

In the definition of a trigger, the MERGE statement cannot be specified directly as a triggered action. An SPL trigger routine that is called in a triggered action, however, can issue the MERGE statement.

Security Policies and Secure Auditing

If the source object or any of its columns is protected by a label-based access control (LBAC) 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 table in MERGE operations.

If the target 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 write in the target object columns that the SET clause or the VALUES clause specifies, or to delete rows from the target that include protected data.

If both the source and the target table are protected, they must be protected by the same security policy. The MERGE statement cannot join tables that are protected by different LBAC security policies.

On Informix® instances that use the secure-auditing facility to record activity that could potentially alter or reveal data or the auditing configuration, no specific audit event mnemonic is defined in audit trails for the MERGE statement:
  • Activities specified by the Delete clause are recorded as DELETE events.
  • Activities specified by the Insert clause are recorded as INSERT events.
  • Activities specified by the Update clause are recorded as UPDATE events.
1 See VALUES Clause
2 See SET Clause
3 Use this path no more than once