Guru: More About Merge

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: