Another method for updating large amounts of data in a table is to perform an update operation using the MERGE statement. When you merge data, you can specify what actions to take when rows from the source data match or do not match the rows in the target data.

Defining the merge behavior

The following is an abbreviated version of the MERGE statement syntax for the purposes of explanation. For the full syntax of the MERGE statement, see MERGE statement.

MERGE INTO target-object
USING source-object
ON merge-search-condition
{ WHEN MATCHED | WHEN NOT MATCHED } [...]

When the database performs a merge operation, it compares rows in source-object to rows in target-object to find rows that either match or do not match according to the definition contained in the ON clause. Rows in source-object are considered a match if there exists at least one row in target-table such that merge-search-condition evaluates to true.

source-object can be a base table, view, materialized view, derived table, or the results of a procedure. target-object can be any of these objects except for materialized views and procedures. For further restrictions on these object types, see MERGE statement.

The ANSI SQL/2003 standard does not allow rows in target-object to be updated by more than one row in source-object during a merge operation.

Once a row in source-object is considered matching or non-matching, it is evaluated against the respective matching or non-matching WHEN clauses (WHEN MATCHED or WHEN NOT MATCHED). A WHEN MATCHED clause defines an action to perform on the row in target-object (for example, WHEN MATCHED ... UPDATE specifies to update the row in target-object). A WHEN NOT MATCHED clause defines an action to perform on the target-object using non-matching rows of the source-object.

You can specify unlimited WHEN clauses; they are processed in the order in which you specify them. You can also use the AND clause within a WHEN clause to specify actions against a subset of rows. For example, the following WHEN clauses define different actions to perform depending on the value of the Quantity column for matching rows:

WHEN MATCHED AND myTargetTable.Quantity<=500 THEN SKIP 
WHEN MATCHED AND myTargetTable.Quantity>500 THEN UPDATE SET myTargetTable.Quantity=500

Branches in a merge operation

The grouping of matched and non-matched rows by action is referred to as branching, and each group is referred to as a branch. A branch is equivalent to a single WHEN MATCHED or WHEN NOT MATCHED clause. For example, one branch might contain the set of non-matching rows from source-object that must be inserted. Execution of the branch actions begins only after all branching activities are complete (all rows in source-object have been evaluated). The database server begins executing the branch actions according to the order in which the WHEN clauses were specified.

Once a non-matching row from source-object or a pair of matching rows from source-object and target-object is placed in a branch, it is not evaluated against the succeeding branches. This makes the order in which you specify WHEN clauses significant.

A row in source-object that is considered a match or non-match, but does not belong to any branch (that is, it does not satisfy any WHEN clause) is ignored. This can occur when the WHEN clauses contain AND clauses, and the row does not satisfy any of the AND clause conditions. In this case, the row is ignored since no action is defined for it.

In the transaction log, actions that modify data are recorded as individual INSERT, UPDATE, and DELETE statements.

Triggers defined on the target table

Triggers fire normally as each INSERT, UPDATE, and DELETE statement is executed during the merge operation. For example, when processing a branch that has an UPDATE action defined for it, the database server:

Triggers on target-table can cause conflicts during a merge operation if it impacts rows that will be updated in another branch. For example, suppose an action is performed on row A, causing a trigger to fire that deletes row B. However, row B has an action defined for it that has not yet been performed. When an action cannot be performed on a row, the merge operation fails, all changes are rolled back, and an error is returned.

A trigger defined with more than one trigger action is treated as if it has been specified once for each of the trigger actions with the same body (that is, it is equivalent to defining separate triggers, each with a single trigger action).

Considerations for immediate materialized views

Database server performance might be affected if the MERGE statement updates a large number of rows. To update numerous rows, consider truncating data in dependent immediate materialized views before executing the MERGE statement on a table. After executing the MERGE statement, execute a REFRESH MATERIALIZED VIEW statement. See REFRESH MATERIALIZED VIEW statement and TRUNCATE statement.

Considerations for text indexes

Database server performance might be affected if the MERGE statement updates a large number of rows. Consider dropping dependent text indexes before executing the MERGE statement on a table. After executing the MERGE statement, recreate the text index. See DROP TEXT INDEX statement , and CREATE TEXT INDEX statement.

Example 1

Suppose you own a small business selling jackets and sweaters. Prices on material for the jackets have gone up by 5% and you want to adjust your prices to match. Using the following CREATE TABLE statement, you create a small table called myProducts to hold current pricing information for the jackets and sweaters you sell. The subsequent INSERT statements populate myProducts with data.

CREATE TABLE myProducts (
   product_id    NUMERIC(10),
   product_name  CHAR(20),
   product_size  CHAR(20),
   product_price NUMERIC(14,2));
INSERT INTO myProducts VALUES (1, 'Jacket', 'Small', 29.99);
INSERT INTO myProducts VALUES (2, 'Jacket', 'Medium', 29.99);
INSERT INTO myProducts VALUES (3, 'Jacket', 'Large', 39.99);
INSERT INTO myProducts VALUES (4, 'Sweater', 'Small', 18.99);
INSERT INTO myProducts VALUES (5, 'Sweater', 'Medium', 18.99);
INSERT INTO myProducts VALUES (6, 'Sweater', 'Large', 19.99);
SELECT * FROM myProducts;

Now, use the following statement to create another table called myPrices to hold information about the price changes for jackets. A SELECT statement is added at the end so that you can see the contents of the myPrices table before the merge operation is performed.

CREATE TABLE myPrices (
   product_id    NUMERIC(10), 
   product_name  CHAR(20), 
   product_size  CHAR(20), 
   product_price NUMERIC(14,2), 
   new_price     NUMERIC(14,2));
INSERT INTO myPrices (product_id) VALUES (1);
INSERT INTO myPrices (product_id) VALUES (2);
INSERT INTO myPrices (product_id) VALUES (3);
INSERT INTO myPrices (product_id) VALUES (4);
INSERT INTO myPrices (product_id) VALUES (5);
INSERT INTO myPrices (product_id) VALUES (6);
COMMIT;
SELECT * FROM myPrices;

Use the following MERGE statement to merge data from the myProducts table into the myPrices table. Notice that the source-object is a derived table that has been filtered to contain only those rows where product_name is Jacket. Notice also that the ON clause specifies that rows in the target-object and source-object match if the values in their product_id columns match.

MERGE INTO myPrices p
USING ( SELECT 
     product_id, 
     product_name, 
     product_size, 
     product_price
  FROM myProducts
  WHERE product_name='Jacket') pp
ON (p.product_id = pp.product_id)
WHEN MATCHED THEN
  UPDATE SET 
    p.product_id=pp.product_id,
    p.product_name=pp.product_name,
    p.product_size=pp.product_size,
    p.product_price=pp.product_price,
    p.new_price=pp.product_price * 1.05;
SELECT * FROM myPrices;

The column values for product_id 4, 5, and 6 remain NULL because those products did not match any of the rows in the myProducts table whose products were (product_name='Jacket').

Example 2

The following example merges rows from two fictitious tables, mySourceTable and myTargetTable, using the primary key values of myTargetTable to match rows. That is, if a row in mySourceTable has the same value as the primary key column of myTargetTable, the row is considered a match.

MERGE INTO myTargetTable
   USING mySourceTable ON PRIMARY KEY
   WHEN NOT MATCHED THEN INSERT
   WHEN MATCHED THEN UPDATE;

The WHEN NOT MATCHED THEN INSERT clause specifies that rows found in mySourceTable that are not found in myTargetTable must be added to myTargetTable. The WHEN MATCHED THEN UPDATE clause specifies that the matching rows of myTargetTable are updated to the values in mySourceTable.

The following syntax is equivalent to the syntax above. It assumes that myTargetTable has the columns (I1, I2, .. In) and that the primary key is defined on columns (I1, I2). The mySourceTable has the columns (U1, U2, .. Un).

MERGE INTO myTargetTable ( I1, I2, .. ., In )
   USING mySourceTable ON myTargetTable.I1 = mySourceTable.U1
      AND myTargetTable.I2 = mySourceTable.U2
   WHEN NOT MATCHED 
      THEN INSERT ( I1, I2, .. In ) 
         VALUES ( mySourceTable.U1, mySourceTable.U2, ..., mySourceTable.Un )
   WHEN MATCHED 
      THEN UPDATE SET
      myTargetTable.I1 = mySourceTable.U1,
      myTargetTable.I2 = mySourceTable.U2,
      ...
      myTargetTable.In = mySourceTable.Un;

Using the RAISERROR action

One of the actions you can specify for a match or non-match action is RAISERROR. RAISERROR allows you to fail the merge operation if the condition of a WHEN clause is met.

When you specify RAISERROR, the database server returns SQLSTATE 23510 and SQLCODE -1254, by default. Optionally, you can customize the SQLCODE that is returned by specifying the error_number parameter after the RAISERROR keyword. See MERGE statement.

Specifying a custom SQLCODE can be beneficial when, later, you are trying to determine the specific circumstances that caused the error to be raised.

The custom SQLCODE must be a positive integer greater than 17000, and can be specified either as a number or a variable.

The following statements provide a simple demonstration of how customizing a custom SQLCODE affects what is returned:

Create the table targetTable as follows:

CREATE TABLE targetTable( c1 int );
INSERT INTO targetTable VALUES( 1 );
COMMIT;

The following statement returns an error with SQLSTATE = '23510' and SQLCODE = -1254:

MERGE INTO targetTable
   USING (SELECT 1 c1 ) AS sourceData
   ON targetTable.c1 = sourceData.c1
   WHEN MATCHED THEN RAISERROR;
SELECT sqlstate, sqlcode;

The following statement returns an error with SQLSTATE = '23510' and SQLCODE = -17001:

MERGE INTO targetTable
   USING (SELECT 1 c1 ) AS sourceData
   ON targetTable.c1 = sourceData.c1
   WHEN MATCHED THEN RAISERROR 17001
   WHEN NOT MATCHED THEN RAISERROR 17002;
SELECT sqlstate, sqlcode;

The following statement returns an error with SQLSTATE = '23510' and SQLCODE = -17002:

MERGE INTO targetTable
   USING (SELECT 2 c1 ) AS sourceData
   ON targetTable.c1 = sourceData.c1
   WHEN MATCHED THEN RAISERROR 17001
   WHEN NOT MATCHED THEN RAISERROR 17002;
SELECT sqlstate, sqlcode;