Guru: More About Merge

November 5, 2018 Ted Holt

I often read back through articles that have appeared in this august publication to look for errors and omissions. Such an expedition recently made me aware that I have not told you as much as I would like to about the SQL MERGE statement. Today I am pleased to provide more information.

First I want to be sure that everybody understands is that you can add conditions to the WHEN MATCHED and WHEN NOT MATCHED expressions. That means that you do not have to treat all matched or unmatched rows in the same way. Look at this example:

merge . . .
when not matched and src.Action = 1 then
. . .
when matched and src.Action = 2 then
. . .
when matched and src.Action = 3 then
. . .
when matched then
. . .
when not matched then
. . .

Here are five tests: three for matched rows and two for unmatched rows. As with the CASE structure that we often use in SELECT statements, MERGE will execute the operation of the first condition that proves true. The last two tests are “catch-all” tests and execute only when the action column of the source dataset has an invalid value.

The three periods following each test stand for an operation, and this brings me to the second thing I want to share today.

You may remember from previous articles that MERGE is a combination of INSERT and UPDATE. Well, it’s more than that. There are four — not two — operations that MERGE can carry out. MERGE can also delete rows and raise error conditions. Here’s the full MERGE statement using all four operations.

merge into releases as tgt
   using (select * from relupdates) as src
      on (tgt.PONumber = src.PONumber
     and  tgt.LineNumber = src.LineNumber
     and  tgt.ReleaseNumber = src.ReleaseNumber)
when not matched and src.Action = 1 then
   insert (PONumber, LineNumber, ReleaseNumber,
           ReleaseDate, Quantity)
      values (src.PONumber, src.LineNumber, 
	          src.ReleaseNumber, src.ReleaseDate,
			  src.Quantity)
when matched and src.Action = 2 then
   update set tgt.ReleaseDate = src.ReleaseDate,
              tgt.Quantity    = src.Quantity
when matched and src.Action = 3 then
   delete
when matched then
   signal sqlstate '87501'
      set message_text = 'Error on matched'
when not matched then
   signal sqlstate '87502'
      set message_text = 'Error on unmatched';

This MERGE statement might be the sort of thing you would use in a purchasing application. The database has a file of blanket purchase order releases:

create table releases
  (PONumber      dec(5),
   LineNumber    dec(3),
   ReleaseNumber dec(3),
   ReleaseDate   date,
   Quantity      dec(3),
  primary key (PONumber, LineNumber, ReleaseNumber));

Another table contains a batch of changes to be applied to the releases.

create table relupdates
  (Sequence      dec(3),
   PONumber      dec(5),
   LineNumber    dec(3),
   ReleaseNumber dec(3),
   Action        dec(1),
   ReleaseDate   date,
   Quantity      dec(3),
  primary key (Sequence));

The ACTION column (field) tells what to do to the release.

A batch of transactions would look like this:

The first three transactions are valid.

Transaction 1 updates the date and quantity of an existing release.

when matched and src.Action = 2 then
   update set tgt.ReleaseDate = src.ReleaseDate,
              tgt.Quantity    = src.Quantity

Transaction 2 adds a new release.

when not matched and src.Action = 1 then
   insert (PONumber, LineNumber, ReleaseNumber,
           ReleaseDate, Quantity)
      values (src.PONumber, src.LineNumber, 
	          src.ReleaseNumber, src.ReleaseDate,
			  src.Quantity)

Transaction 3 deletes an existing release.

when matched and src.Action = 3 then
   delete

Transaction 4 is invalid, as there is no release 7 for line 1 of purchase order 213. SIGNAL raises a condition with SQLSTATE 87502.

when not matched then
   signal sqlstate '87502'
      set message_text = 'Error on unmatched';

MERGE is powerful. The more I use it, the more I like it.

RELATED STORIES

A More Efficient Way To Merge With SQL

The Powerful SQL Upsert

Updating Through A Join With SQL, Take Three

Merge Into the Synchronization Fast Lane with DB2 for i 7.1

IBM Knowledge Center – MERGE

IBM Knowledge Center – SIGNAL