In simple terms, the MERGE statement compares key fields between two tables and then modifies one table based on the results of that comparison. This helps in managing data effectively. While the MERGE statement might seem more complicated than basic INSERTs or UPDATEs at first, once you grasp its concept, you’ll find it more convenient to use than handling INSERTs or UPDATEs separately.

Performance considerations for the SQL MERGE statement

The MERGE statement’s efficiency relies heavily on using the right indexes for matching the source and target tables. It’s important to optimize the join conditions and filter the source table to fetch only the required records for the statement to perform its tasks effectively.

Let’s look at example below to have a better understanding.

Imagine you have two tables named source and target. You’re tasked with updating the target table based on matching values from the source table. Here are three scenarios to consider:

• Some rows exist in the source table but not in the target table. In this situation, you’ll need to insert these rows from the source table into the target table.
• Both the source and target tables contain rows with identical keys, but their non-key column values differ. In this case, you’ll need to update the rows in the target table with the corresponding values from the source table.
• There are rows in the target table that don’t exist in the source table. Here, you can keep these unmatched rows.

Using INSERT, UPDATE, and DELETE statements separately requires constructing three distinct statements to update data in the target table with matching rows from the source table.

However, DB2 for i simplifies this process with the MERGE statement, enabling you to perform all three actions simultaneously. Here’s the example of the MERGE statement:

MERGE INTO tbl_target target USING tbl_source source
     ON target.EMPNO = source.EMPNO
          AND target.EMPDEP = source.EMPDEP
WHEN NOT MATCHED THEN
   INSERT VALUES(EMPNO, EMPADR, EMPLVL, EMPSAL, EMPDEP)
WHEN MATCHED THEN
   UPDATE SET EMPSAL = source.SALARY

The statement above compares rows in the target table with those in the source table based on the EMPNO and EMPDEP columns. These columns are the primary keys, so that unique records are selected.

For any row in the source table without a matching EMPNO and EMPDEP row in the target table (NOT MATCHED), a record INSERT is performed. It involves adding a new row to the target table, including the EMPNO,EMPADR, EMPLVL, EMPSAL and EMPDEP values from the source table.

On the other hand, for rows in the source table that do have corresponding rows in the target table (MATCHED), the EMPSAL value in the target table’s row is updated with the value from the source table.

Deletion using MERGE statement:

SQL MERGE can also be used to delete records from the table. Below is an example where employee having EMPLVL less than 2 are deleted from target table.

MERGE INTO tbl_target target USING tbl_source source
     ON target.EMPNO = source.EMPNO
          AND target.EMPDEP = source.EMPDEP.

WHEN MATCHED and EMPLVL < 2
   THEN DELETE

WHEN MATCHED THEN
   UPDATE SET EMPSAL = source.SALARY