Mergeについての追加情報

筆者は、この威厳ある刊行物に掲載された記事について、何か漏れや間違いがないかと、たびたび記事の読み直しを行っています。そうした中で、先日、SQL MERGEステートメントに関して、思っていたほど多くのことを説明できていなかったことに気付きました。この記事では、そうした書き残しとなっていた事柄を追加情報として記したいと思います。

最初に、誰もが理解していると確認しておきたいことは、WHEN MATCHEDおよびWHEN NOT MATCHED式に条件を追加できるということです。つまり、すべてのマッチする行またはマッチしない行を同じように処理する必要はありません。次の例を見てください。

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
. . .

5つのテストがあり、3つはマッチする行のテスト、2つはマッチしない行のテストです。SELECTステートメントでよく使用するCASE構造と同様に、MERGEは、真と判定された最初の条件の操作を実行します。最後の2つのテストは「包括的な」テストであり、ソース データセットのアクションの列の値が無効なときにのみ実行されます。

それぞれのテストの後に続く3つのピリオドは、実行される操作を表しています。そして、このことから、この記事で紹介する2つ目のポイントへとつながって行きます。

以前の記事で、MERGEはINSERTとUPDATEを組み合わせたものだと述べたことを覚えておいででしょうか。ただし、それだけではありません。MERGEが実行できる操作は、2つではなく、4つあります。MERGEは、行を削除したり、エラー条件を発生させたりすることもできます。以下に、4つすべての操作を使用する完全なMERGEステートメントを示します。

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';

このMERGEステートメントは、購買管理アプリケーションで使用しているようなものかもしれません。データベースに、包括注文のリリースのファイルがあります。

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

注文番号	品目番号	リリース番号	リリース日	数量
101	4	1	2018-11-05	12
101	4	2	2018-11-12	10
101	4	3	2018-11-19	8
213	1	1	2018-11-12	6
213	1	2	2018-11-19	8

もうひとつの表には、リリースに適用される一連の変更処理が含まれています。

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));

ACTION列（フィールド）は、リリースに対してどのような操作を行うかを指示します。

アクション	説明
1	リリースを追加する
2	リリースを変更する
3	リリースを削除する

一連のトランザクションは次のようになります。

シーケンス番号	注文番号	品目番号	リリース番号	アクション	リリース日	数量
1	101	4	3	2	2018-11-24	10
2	101	4	4	1	2018-11-28	16
3	213	1	2	3	2018-11-01	0
4	213	1	7	2	2018-11-30	14

最初の3つのトランザクションは有効です。

トランザクション1は、既存のリリースのリリース日および数量を更新します。

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

トランザクション2は、新たなリリースを追加します。

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)

トランザクション3は、既存のリリースを削除します。

when matched and src.Action = 3 then
   delete

トランザクション4は、注文番号213の品目番号1でリリース番号7がないために無効になります。SIGNALは、SQLSTATE 87502の条件を発生させます。

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

MERGEは強力です。使えば使うほど、好きになるはずです。

あわせて読みたい記事