はじめに
DB2 for i 7.1で新たにサポートされるようになったSQLステートメントの一つにMERGEステートメントがあります。MERGEステートメントは2つのテーブルのデータを同期する際などに利用できるステートメントです。複数テーブル間のデータ同期はプログラムの作成でも実現可能ですが、MERGEステートメントを利用することで、より手軽でシンプルに実現できます。
ここでは単純な例を考えてみます。2つのテーブル、YEARSALESおよびMONTHSALESを考えます。どちらの表もSKUおよびQTYカラムを持っています。SKUがユニーク・キーです。ここで、MONTHSALESからYEARSALESにデータをマージすることを考えると、以下のようなステートメントとなります。
このステートメントは次のような意味となります。
- MONTHSALESの各行に対してSKUが一致するようなYEARSALESの行を探索します。
- 一致する行が存在する場合にはQTYカラムの値をMONTHSALESのQTYカラムの値を利用して更新します。
- 一致する行が存在しない場合にはMONTHSALESの行のSKUおよびQTYの値を持つような新規の行を挿入します。
この例では、INTOターゲットの接尾辞yおよびUSINGソースの接尾辞mは相関名を示しています。相関名を利用するとステートメントが読みやすくなります。
上のSQLステートメントと全く同じ機能を実装したSQLプロシージャーと比較してみましょう。以下のプロシージャー自体も比較的単純ですが、MERGEステートメントほどではないことがお分かりだと思います。
MERGEステートメントの構造
MERGEステートメントは以下の要素から構成されています。
- MERGEによって行が変更される可能性がある1つの“ターゲット”となるテーブル（もしくはビュー）
- table-reference（テーブル参照）と呼ばれるデータ の“ソース”で、ターゲット上でどのような行操作を行うかを判別するためのsearch-condition（比較表現）と共に用いられます。table-referenceは1つテーブルとは限らず、複数のテーブルやビューに対する複雑な照会結果などでも構いません。
- search-conditionと呼ばれる比較で、通常はターゲットのカラムとソースであるtable-referenceのカラムの比較を行い、ターゲット上での行操作を制御します。
- ターゲット上でのINSERT, UPDATE, DELETEのうちのどの行操作を行うかを決めるための1つ以上のmatching-condition（マッチング条件）があります。
INTO句
実行の結果、行の変更が行われるテーブルもしくはビューであるMERGEターゲットを識別します。
USING句
table-referenceとしてのソース・データを識別します。MERGEステートメントで利用する一般的なtable-referenceのタイプは以下の通りです。
- 既述の例にあるような単一のテーブルもしくはビュー：USING sales AS src
- 次のようなSELECTステートメント：USING ( SELECT qty FROM sales ) AS src
- 次のようなテーブル関数：USING ( TABLE( salesfunc(1) ) ) AS src (col1, col2)
なお、USING句中のtable-referenceのタイプに関係なく全て0-N行の論理的な結果表となります。
ON句
ON句には後に続くWHEN句で設定する行操作の対象となるかを判別するための search-conditionが含まれます。
ON ( src.partnbr = tgt.partnbr )
上記のようなソースもしくはターゲットいずれかのカラムに対する単純な比較表現だけではなく、副照会を含むようなより複雑な表現も可能です。ソースの各行に対して、ON句の比較が行われます。評価が真である場合にはWHEN MATCHED句が実行され、偽である場合にはWHEN NOT MATCHED句が実行されます。
WHEN句
それぞれのWHEN句にはmatching-conditionが含まれます。matching-conditionとしては最低限MATCHEDもしくはNOT MATCHEDとそれに続くTHENと行に対する操作を記述します。matching-conditionで利用できる行操作は以下の通りです。
- MATCHED：UPDATE もしくは DELETE
- NOT MATCHED：INSERT
matching-conditionの最も単純な形式は以下の通りです。
- WHEN MATCHED THEN（行操作）
- WHEN NOT MATCHED THEN（行操作）
上記のような単純な形式だけではなく、“AND search-condition”を利用して、以下のようにより細かな選択条件の指定も可能です。
- WHEN MATCHED AND search-condition THEN（行操作）
- WHEN NOT MATCHED AND search-condition THEN（行操作）
このANDに続くsearch-conditionはON句中の比較に追加する比較条件となります。
大抵の場合はWHEN MATCHEDおよびWHEN NOT MATCHED句ひとつずつの指定で大丈夫ですが、複数のWHEN句を利用することも可能です。後述するブック・リストの例では複数のWHEN句をどのように活用するかについて示します。
WHEN MATCHEDもしくはWHEN NOT MATCHED句では行操作を実行するかわりに、SIGNALステートメントを利用してエラーを返すこともできます。例えば前述の例では、廃止されたSKUの判別ロジックは含まれていません。そこで以下のようなステートメントを追加することで、廃止されたSKUを検出し、エラーを返すことができるようになります。
WHEN句中の行操作
WHEN句の操作が実行されるたびに、ターゲット・テーブル内の行に対して何らかの処理が行われます。MATCHEDに該当する場合には既存の行は更新もしくは削除される可能性があり、NOT MATCHEDに該当する場合には新規の行が挿入される可能性があります。
ターゲット・テーブルの行のみが影響を受けるので、WHEN句中のINSERT, UPDATE, DELETE操作のステートメント形式としては、通常とは異なり、ターゲット・テーブル名を指定する必要はありません (前述の最初のMERGE使用例にある通りです)。
INSERTステートメントではVALUES句で新規の行のカラムの値を指定します。もしターゲットのカラムに対応する値が指定されていなければ、null値もしくはデフォルト値という通常の規則が適用されます。また単体のINSERTステートメントと同じく、特殊値であるNULLもしくはDEFAULTはVALUES句中で明示的にコーディングすることもできます。同様に、ホスト変数やパラメーター・マーカーが使用される場合も単体のINSERTステートメントと同じく、NULL値が適用されます。
どのカラムに対して値が挿入されるかを示すためのカラム名指定も可能です。例えば前述の最初の例でカラムを明示指定する場合には、以下のようになります。
UPDATEステートメントはターゲット内のある既存の行の更新を行います。SET句で該当行の新規の値を指定します。単体のUPDATEステートメントと同様に、各カラムを “column-name = ＜値＞” で修飾する形式となります。MERGE内のSET句に関する詳細はおよびオプションについてはDB2 for i SQL解説書のMERGEステートメントのセクションをご参照ください。
IBM i 7.1 DB2 for i SQL解説書：MERGEステートメント
DELETEステートメントは単純にターゲットから既存の行を削除します。MERGE内でのDELETE操作に関してはその他の句はありません。
INSERTおよびUPDATEで可能なカラム値
VALUESおよびSET句はMERGEのターゲット行のカラムに対する新規の値を指定するために利用します。大抵はカラムの値は前述の例の通り、USING table-reference結果表の行がもとになります。
WHEN NOT MATCHED THEN INSERT VALUES(M.SKU, M.QTY)
カラムの値は他の方法で与えることもできます。カラムそれぞれの値は与えられたVALUESもしくはSET句中では他とは互いに独立したものとして、それぞれ以下のような形式で指定できます。
【リテラル】
【SQL組み込み関数】
【SQL特殊レジスター】
【SQL表現】
【ホスト変数】
【パラメーター・マーカー】
【グローバル変数】
もちろん、単体のINSERTもしくはUPDATEステートメントと同様に、カラムの値として与えるデータのデータ・タイプは対応するカラムのデータ・タイプと互換性があることが必要です。
Atomicity オプション
MERGEでは3つのオプションをサポートしています。
- ATOMIC (デフォルト)
- NOT ATOMIC STOP ON SQLEXCEPTION
- NOT ATOMIC CONTINUE ON SQLEXCEPTION
ほとんどの場合はデフォルトである1のATOMICを使用します。ATOMICではコミットメント制御下で実行する場合に、何らかのエラー状態が検出された時点でMERGEによる変更は全て取り消されます。
その他のオプションは特定の状況下で活用するものです。2のNOT ATOMIC STOP ON SQLEXCEPTIONでは、個々の行操作の実行中にエラーが発生した場合にMERGEは終了しますが、それまでの成功した行操作による変更はそのまま有効なものとして残ります。3のNOT ATOMIC CONTINUE ON SQLEXCEPTIONでは、個々の行操作の実行中にエラーが発生した場合に、MERGEは残りのソース行の処理を継続しますので結果として、多くの行操作のエラーが許容されることに繋がります。
複数のWHEN句の選択
大抵のMERGEステートメントは1つのWHEN MATCHEDおよび1つのWHEN NOT MATCHED句で良いと思いますが、より多くのWHEN MATCHEDもしくはWHEN NOT MATCHED句の指定することも可能です。複数のWHEN句を指定した場合は、WHEN句はSQLルーチンのCASE制御ステートメントやC言語におけるswitch文と同じように機能します。
複数のWHEN句があるMERGEステートメントは以下のようなものです。
この例では2つのWHEN MATCHED句と2つのWHEN NOT MATCHED句があります。USING table-reference結果表からの行が処理される際に、論理的に各WHEN句が記述された順で評価されます。最初に真と評価されたWHEN句で指定された行操作が行われます。もしON句が真 (つまり、PARTNOカラムの値が同一である) の状態の場合、全てのWHEN MATCHED句が選択される候補となります。論理的に最初に真と評価されるWHEN MATCHED 句が選択され、その他は全てバイパスされます。もしCOSTカラムの値が101だとすると、一番目のWHEN MATCHED句のAND search-conditionが真なので、そのWHEN句の行操作であるUPDATEが実行されます。複数のWHEN NOT MATCHED句に関しても同様です。
MERGEはUSING table-reference結果表からの行がどのWHEN句にも当てはまらないようなコードとすることもできます。その場合は、当てはまらない行は全て無視されます。MERGEステートメントではELSE IGNORE句を全てのWHEN句の後に記述できますが、この句の存在に関わらず、適合しない行は全て無視されますので、ELSE IGNORE句は単にステートメントを読みやすくするためのものと捉えてもらえれば良いでしょう。
MERGEステートメントの例（基本編）
2つのテーブルがあると仮定して、一方を他方にマージする必要があるとします。パーツのインベントリー表であるINVENTORYテーブルは以下のカラムを持ちます。
入庫した新規パーツが記録されるNEW_RECIEVEDテーブルは以下のカラムを持ちます。
NEW_RECIEVEDテーブルには仕入先から受け取った新規インベントリーに関する情報が格納されます。INVENTORYテーブルに対しては、以下のようなMERGEステートメントを利用して情報の更新を行います。
このステートメントが実行されると、(NEW_RECIEVEDテーブルを基にした) USING結果表の各行がターゲットのINVENTORYテーブルと両者のPART_NOカラムを基に比較されます。一致するINVENTORYテーブルの行が存在する場合、QTY_ONHANDカラムの値にはNEW_RECIEVEDテーブルからのQTY_RCVDカラムの値が加算されます。一致するINVENTORYテーブルの行が存在しない場合には、NEW_RECIEVEDテーブルを基にしたUSING結果表からの各カラムの値を持つ新規行が挿入されます。なお新規行のDATE_ADDEDカラムの値としてはCURRENT DATE特殊レジスターの値を用いています。
MERGEステートメントの例（応用編）
メンバーがある本を各自読んで、定期的にその本についてのディスカッションを行うという読書クラブを例に考えてみます。メンバーは今後読む本の候補についても話し合い、次読む本を決めます。クラブでは過去に読んだ本の名前と著者の名前のリストを単純な集計表 (ブック・リスト) でまとめており、この集計表には候補に挙がっているがまだ読んでいない本も同様にリストしていきます。ブック・リストは定期的に、読み終わった、もしくはメンバーから提案されたという情報に基づいて更新します。この例ではブック・リストとしてDB2 for i のテーブルを利用してMERGEステートメントを利用するとどのように管理できるかを考えてみます。なおブック・リストとして、以下を満たすような仕組みにしたいと考えているとします。
- データ項目としては本のタイトルと著者名に限定する
- 本ごとのレコードの重複は避ける
- ブック・リストの更新を単純化する
ブック・リスト・データベースの作成
後に示すSQL DDL (Data Definition Language：データ定義言語) の通り、ブック・リストは以下のオブジェクトから構成します。
- ブック・レジストリー・テーブル
- 本のタイトルおよび著者名
- ブック・ログ・テーブル
- これまでの累積的な本に対する活動の記録
- ミーティング・ログ・テーブル
- 直近の読書クラブのミーティングにおける本に対する活動の記録
新しい本ごとに、ブック・レジストリー・テーブルに本のタイトルと著者名の行が挿入されます。BookIDカラムはID列 (IDENTITY column) で、各行が挿入される際に、自動的に本ごとに固有な値が割り当てられます。
レジストリー・テーブルに既にその本に該当する行が存在している場合には、その本に対する実施中の活動を、タイトルや著者名を再入力するのではなく、固有のBookIDを使ってブック・ログ・テーブルに追加します。ミーティング・ログ・テーブルにもBookIDカラムはありますので、ブック・ログ・テーブルの累積データにミーティング・ログのデータをマージすることを考えます。ミーティング・ログとブック・ログでは、確実に各行に対応するレジストリー・テーブル内の行が存在するように、ブック・レジストリー・テーブルのBookIDカラムに関連付けた参照制約を使うようにします。
以下がブック・リスト・データベースを実装するためのSQL DDLとなります。
図1は上記3つのオブジェクトの関係性を示したものです。
【図1：ブック・リスト・データベース】
ブック・リストのデータ
以下はブック・レジストリー・テーブルとミーティング・ログ・テーブルの行の例です。BOOKIDカラムの値は、ブック・レジストリー・テーブルに新しい本の行が挿入される際に生成され、ミーティング・ログで関連する本のステータスを登録する際に利用されます。
【BOOKREGテーブル】
| TITLE | AUTHOR | BOOKID |
|---|---|---|
| In the Heart of the Sea | Nathaniel Philbrick | 1 |
| The River of Doubt | Candice Millard | 2 |
| Isaac’s Storm | Erik Larson | 3 |
| The Shipping News | Annie Proulx | 4 |
【MTGLOGテーブル】
| BOOKID | STATUS | WHENREAD |
|---|---|---|
| 1 | P | ＜null＞ |
| 2 | P | ＜null＞ |
| 3 | P | ＜null＞ |
| 4 | R | 2011-01-31 |
ミーティング・ログでブック・ログの更新
ミーティング・ログ・テーブルのデータを、累積データを格納しているブック・ログ・テーブルにマージする際には以下のMERGEステートメントを利用できます。
- USINGで使用されている照会はWHERE句を利用するなどのより複雑なものでも構いません。カラムの値 (選択結果のリスト) は他のテーブルや、ユーザー定義もしくは組み込み関数、特殊レジスターなどに由来する値も可能です。
- UPDATE SETおよびINSERT VALUESで使用されるカラムの値は、USING結果表からのその他のカラムの値に基づいた値となっています。
- UPDATEとINSERTのいずれを実行するべきかを判別するために、追加のsearch-conditionがWHEN MATCHEDおよびWHEN NOT MATCHED句で使用されています。
まとめ
ここまで見てきましたように、MERGEステートメントはSQLのツール、テクニックとしてとても便利なものです。始めはすこし複雑に思えるかも知れませんが、試してみると思ったよりも簡単ですので、是非ご活用頂ければと思います。
【参考】
IBM、IBMロゴ、ibm.comおよびSystem Storageは、世界の多くの国で登録されたInternational Business Machines Corporationの商標です。他の製品名およびサービス名等は、それぞれIBMまたは各社の商標である場合があります。現時点でのIBMの商標リストについては、www.ibm.com/legal/copytrade.shtml（US）をご覧ください。