Merging data

You can use the MERGE statement to update a target table from another table, a derived table, or any other table-reference. This other table is called the source table. The simplest form of a source table is a list of values.

Based on whether or not a row from the source table exists in the target table, the MERGE statement can insert a new row, or update or delete the matching row.

The most basic form of a MERGE statement is one where a new row is to be inserted if it doesn't already exist, or updated if it does exist. Rather than attempting the insert or update and, based on the SQLCODE or SQLSTATE, then trying the other option, by using the MERGE statement the appropriate action will be performed.

In this example, you want to add or replace a row for a department. If the department does not already exist, a row will be inserted. If the department does exist, information for the department will be updated.

MERGE INTO DEPARTMENT USING 
          (VALUES ('K22', 'BRANCH OFFICE K2', 'E01')) INSROW (DEPTNO, DEPTNAME, ADMRDEPT)
  ON DEPARTMENT.DEPTNO = INSROW.DEPTNO
  WHEN NOT MATCHED THEN
     INSERT VALUES(INSROW.DEPTNO, INSROW.DEPTNAME, INSROW.ADMRDEPT)
  WHEN MATCHED THEN 
     UPDATE SET DEPTNAME = INSROW.DEPTNAME, ADMRDEPT = INSROW.ASMRDEPT

Suppose you have a temporary table that is a copy of the EMP_PHOTO table. In this table, you have added photo information for new employees and updated the photo for existing employees. The temporary table only contains changes, no rows for unchanged photo information.

To merge these updates into the master EMP_PHOTO table, you can use the following MERGE statement.

MERGE INTO EMP_PHOTO target USING TEMP_EMP_PHOTO source
     ON target.EMPNO = source.EMPNO
          AND target.PHOTO_FORMAT = source.PHOTO_FORMAT
WHEN NOT MATCHED THEN
   INSERT VALUES(EMPNO, PHOTO_FORMAT, PICTURE)
WHEN MATCHED THEN
   UPDATE SET PICTURE = source.PICTURE

When this statement is run, the rows in the target table are compared to the rows in the source table using the EMPNO and PHOTO_FORMAT columns. These are the columns that make up the primary key for the table, so they guarantee uniqueness. Any row that is in the source table that does not have a matching EMPNO and PHOTO_FORMAT row in the target table (NOT MATCHED) will perform the INSERT action. In this case, it will insert a new row into the target table containing the EMPNO, PHOTO_FORMAT, and PICTURE values from the source table. Any row in the source table that already has a corresponding row in the target table (MATCHED) will have the target table's row updated with the PICTURE value from the source table.

To make the merge a little bit more complex, let's add a column to the EMP_PHOTO table.

ALTER TABLE EMP_PHOTO ADD COLUMN LAST_CHANGED TIMESTAMP 
                            GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Now, let us assume that the person who maintains the TEMP_EMP_PHOTO table has some rows in the temporary table that have already been merged into the master copy of the EMP_PHOTO table. When doing the MERGE, you don't want to update the same rows again since the values have not changed. It is also possible that someone else has updated the master EMP_PHOTO with a more recent picture.

MERGE INTO EMP_PHOTO target USING TEMP_EMP_PHOTO source
     ON target.EMPNO = source.EMPNO
          AND target.PHOTO_FORMAT = source.PHOTO_FORMAT
WHEN NOT MATCHED THEN
   INSERT VALUES(EMPNO, PHOTO_FORMAT, PICTURE, LAST_CHANGED)
WHEN MATCHED AND target.LAST_CHANGED < source.LAST_CHANGED THEN
   UPDATE SET PICTURE = source.PICTURE,
              LAST_CHANGED = source.LAST_CHANGED

This statement has an extra condition added to the MATCHED clause. Adding the comparison of the LAST_CHANGED column will prevent the master EMP_PHOTO table from being updated with a photo that has a timestamp older than the current master's timestamp.