Simplifying Your Application Development with the SQL MERGE Statement - Page 2

by Roman B. Melnyk and Paul C. Zikopoulos

The SQL MERGE Statement in DB2 UDB Version 8.1.2

To illustrate the basic function of the MERGE statement using the SAMPLE database that comes with DB2 UDB, consider the EMPLOYEE table to be the "master" table that contains up-to-date information about the employees of a large corporation. This company's branch offices handle updates to their employee records by maintaining their own version of the EMPLOYEE table called EMP_TEMP. The corporation's EMPLOYEE table can be updated regularly by invoking merge operations to consolidate the information that is contained in the EMP_TEMP table.

We could summarize the business problem as follows:

For each record in the EMP_TEMP table
	Find the corresponding record in the EMPLOYEE table
	If the record does not exist in the EMPLOYEE table
		Insert this record into the EMPLOYEE table
	Else
		Update existing data in the EMPLOYEE table
	End If
End For

We could enhance the logic to include error reporting in the event that a specific record does not exist. The possibilities are endless, but for the sake of clarity, we will consider the simpler scenario.

For example, the EMP_TEMP table could be defined as follows:

	db2 create table emp_temp like employee

The SQL language has a simple way of creating a table based on an existing table definition, using the keyword LIKE. This statement creates the EMP_TEMP table to be exactly like the EMPLOYEE table. Now populate the EMP_TEMP table using the following INSERT statements:

db2 insert into emp_temp values
  ('000011', 'TERESA', 'M', 'TALLERICO', 'A00',
   '1234', '05/05/1990', 'ANALYST', 22, 'F', '04/02/1963',
   99000.00, 9000.00, 20000.00)

db2 insert into emp_temp
  (empno, firstnme, midinit, lastname, edlevel,
   salary, bonus, comm) values
  ('000110', 'VINCENZO', 'G', 'LUCCHESSI', 19,
   66600.00, 1234.00, 4321.00)

The first statement inserts a full row for new employee number 000011. The second statement inserts updated compensation data for existing employee number 000110.

Suppose we now issue a SELECT on employee 000011 against the EMPLOYEE table:

	db2 select * from employee where empno = '000011'

The query, of course, returns 0 records, because the contents of the EMP_TEMP table have not yet been integrated (merged) into the EMPLOYEE table.

Now suppose we issue this query:

	db2 select * from employee where empno = '000110'

It returns the record for employee 000110 with old values for the SALARY, BONUS and COMM columns:

EMPNO         SALARY      BONUS       COMM
------ ...... ----------- ----------- -----------
000110           46500.00      900.00     3720.00

  1 record(s) selected.

Now we are ready to assemble a MERGE statement that will integrate data in the EMP_TEMP table with that in the EMPLOYEE table.

Following is a syntax diagram for the MERGE statement showing all of its principal clauses:

In its simplest form (the form that we will use in our example), the MERGE statement looks like this:

MERGE INTO <table-name> AS <correlation-name>
	USING <table-reference> ON <search-condition>
	WHEN <matching-condition> THEN <modification-operation>

We are specifying the name of the table into which data will be merged. We are going to assign a correlation name to that table to avoid ambiguous table references in the search condition. We will also identify the columns to be considered in the EMP_TEMP table. Finally, we will specify the actions that are to be taken when a record in the EMP_TEMP table is found to have a match in the EMPLOYEE table, and when it does not have a match.

Here is the resulting MERGE statement, which will take the contents of the EMP_TEMP table and merge them with the EMPLOYEE table. We are showing the keywords in uppercase characters merely for the sake of clarity; you are free to specify the keywords in lowercase characters if you prefer.

MERGE INTO employee AS e
  USING (SELECT
   empno, firstnme, midinit, lastname, workdept, phoneno,
   hiredate, job, edlevel, sex, birthdate, salary, bonus, comm
   FROM emp_temp) AS et
  ON e.empno = et.empno
  WHEN MATCHED THEN
    UPDATE SET (salary, bonus, comm) =
               (et.salary, et.bonus, et.comm)
  WHEN NOT MATCHED THEN
    INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,
      hiredate, job, edlevel, sex, birthdate, salary, bonus,
      comm)
      VALUES (et.empno, et.firstnme, et.midinit, et.lastname,
        et.workdept, et.phoneno, et.hiredate, et.job, et.edlevel,
        et.sex, et.birthdate, et.salary, et.bonus, et.comm)

Suppose we again issue a SELECT on employee 000011 against the EMPLOYEE table:

	db2 select * from employee where empno = '000011'

The query this time returns 1 record, because the contents of the EMP_TEMP table have now been integrated into the EMPLOYEE table.

And suppose we issue this query again:

	db2 select * from employee where empno = '000110'

This time, it returns the record for employee 000110 with new values for the SALARY, BONUS and COMM columns:

EMPNO         SALARY      BONUS       COMM
------ ...... ----------- ----------- -----------
000110           66600.00     1234.00     4321.00

  1 record(s) selected.

Here are some important points to remember about the MERGE statement:

• The authorization ID of the statement must have the appropriate privileges to perform update, insert, or delete operations on the target table.
• The authorization ID must also have the appropriate privileges on the table referenced in the subquery.
• Each row in the target table can be operated on only once within a single MERGE statement, meaning that a row in the target table can be identified as MATCHED with only one row in the result table of the table reference.

Splurge with the MERGE

As you can see, the MERGE statement can be a very valuable addition to your programming repertoire and the way you implement your business logic. We recommend that you experiment to learn more about this feature by exploring more complex real-world business problems. You could, for example, include additional search conditions on a WHERE clause to invoke different update, insert, or delete operations, depending on the statement's sub-condition. A store might only accept a returned item that it does not normally have in stock if it has an inventory-sharing cost structure with the originating store.

Have fun!

About the Authors

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.