Inserting data and updating data in a single operation

You can update existing data and insert new data in a single operation. This operation is useful when you want to update a table with a set of rows, some of which are changes to existing rows and some of which are new rows.

About this task

You can update existing data and insert new data in a single operation by using the MERGE statement.

For example, an application might request a set of rows from a database, enable a user to modify the data through a GUI, and then store the modified data in the database. Some of this modified data is updates to existing rows, and some of this data is new rows. You can do these update and insert operations in one step.

Procedure

To update existing data and insert new data in a single operation:

Issue a MERGE statement.

To update existing data and inserting new data, specify a MERGE statement with the WHEN MATCHED and WHEN NOT MATCHED clauses. These clauses specify how Db2 handles matched and unmatched data. If Db2 finds a matching row, that row is updated. If Db2 does not find a matching row, a new row is inserted.

Example

Suppose that you need to update the inventory at a car dealership. You need to add new car models to the inventory and update information about car models that are already in the inventory.

You could make these changes with the following series of statements:

UPDATE INVENTORY
  SET QUANTITY = QUANTITY + :hv_delta
  WHERE MODEL = :hv_model;

--begin pseudo code
if sqlcode >= 0  
then do   
       GD
       if rc = 0  then INSERT..
     end                         
-- end pseudo code

GET DIAGNOSTICS :rc = ROW_COUNT;

IF rc = 0 THEN
INSERT INTO INVENTORY VALUES (:hv_model, :hv_delta);
END IF;

The MERGE statement simplifies the update and the insert into a single statement:

MERGE INTO INVENTORY
USING ( VALUES (:hv_model, :hv_delta) ) AS SOURCE(MODEL, DELTA)
ON INVENTORY.MODEL = SOURCE.MODEL
  WHEN MATCHED THEN UPDATE SET QUANTITY = QUANTITY + SOURCE.DELTA
  WHEN NOT MATCHED THEN INSERT VALUES (SOURCE.MODEL, SOURCE.DELTA)
NOT ATOMIC CONTINUE ON SQLEXCEPTION;