Simplifying Your Application Development with the SQL MERGE Statement

Monday Mar 8th 2004 by DatabaseJournal.com Staff
Share:

This article outlines SQL MERGE statement support in the IBM DB2 Universal Database (DB2 UDB) products as of Version 8.1.2. The MERGE statement combines conditional update, insert and delete operations on a target table or updatable view.

by Roman B. Melnyk and Paul C. Zikopoulos

Today's efficient businesses strive to balance the cost savings of a just-in-time (JIT) inventory system with the potential for lost sales resulting from stock-out situations. A common database design for a JIT procurement system is to have a "master" table that contains up-to-date information about a particular domain of the business. One example is a PARTS table that tracks inventory. When key performance indicators in this table fall below certain thresholds, business logic is invoked to replenish the stock, thereby avoiding stock-out conditions and lost sales. Another table (for example, an ORDERS table) handles transactions. This table contains the changes that must be applied to the master table to consolidate inventory levels for future sales. For example, a particular item was ordered and now the on-hand inventory must be decreased by one. The ORDERS table could also include returned product that was shipped from a different distribution center. This "not-on-hand" type of inventory must also be accounted for.

An application designed to manage these types of inventory must be able to:

  • Accommodate changes to existing stock levels. This is handled through update operations.
  • Add new items to the inventory as a result of returns that have not yet been buffered through a particular distribution center. This is handled through insert operations.

Traditionally, an application developer would have to code separate UPDATE and INSERT statements into an application to address these requirements. These functions are typically exposed through some type of graphical user interface (GUI) that a customer service representative uses to query the database and to modify the data when, for example, a customer picks up an order or returns a product. Combined update, insert and even delete operations ("merge" operations, in effect) would enable a user to work through the GUI while the underlying application efficiently "hardens" the changes in the database.

This article outlines SQL MERGE statement support in the IBM DB2 Universal Database (DB2 UDB) products as of Version 8.1.2. The MERGE statement combines conditional update, insert and delete operations on a target table or updatable view.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved