7. MERGE文
新しいMERGE文を使用すると、1つのテーブルから別のテーブルへデータをコピーしなければならないときに、INSERT、UPDATEおよびDELETEのロジックで集合指向の処理を行うことができます。簡単な例を紹介しましょう。まず、次に示す入力テーブルとトランザクションセットを見てください。
CREATE TABLE transaction ( part_number INTEGER NOT NULL PRIMARY KEY, action VARCHAR ( 6 ) NOT NULL, description LONG VARCHAR NULL, price DECIMAL ( 11, 2 ) NULL ); INSERT transaction VALUES ( 1, 'Add', 'widget', 10.00 ); INSERT transaction VALUES ( 2, 'Add', 'gizmo', 20.00 ); INSERT transaction VALUES ( 3, 'Add', 'thingie', 30.00 ); INSERT transaction VALUES ( 4, 'Add', 'bouncer', 40.00 );
対応する出力テーブルと、上記の4つの入力'Add'トランザクションを1つのINSERT操作にまとめるMERGE文を次に示します。
CREATE TABLE part ( part_number INTEGER NOT NULL PRIMARY KEY, description LONG VARCHAR NOT NULL, price DECIMAL ( 11, 2 ) NOT NULL, exceptions LONG VARCHAR NOT NULL DEFAULT '' ); MERGE INTO part USING transaction ON PRIMARY KEY WHEN NOT MATCHED AND transaction.action = 'Add' THEN INSERT ( part_number, description, price ) VALUES ( transaction.part_number, transaction.description, transaction.price ) WHEN MATCHED AND transaction.action = 'Change' THEN UPDATE SET part.description = COALESCE ( transaction.description, part.description ), part.price = COALESCE ( transaction.price, part.price ) WHEN MATCHED AND transaction.action = 'Delete' THEN DELETE WHEN MATCHED AND transaction.action = 'Add' THEN UPDATE SET part.exceptions = STRING ( part.exceptions, 'Duplicate Add ignored. ' ) WHEN NOT MATCHED AND transaction.action = 'Change' THEN INSERT VALUES ( transaction.part_number, transaction.description, transaction.price, 'Change accepted, new part inserted. ' ) WHEN NOT MATCHED AND transaction.action = 'Delete' THEN SKIP;
このMERGE文の意味は次のとおりです。
- INTO句で、出力テーブルつまりターゲットテーブルを指定します。
- USING句で、入力テーブルつまりソーステーブルを指定します。
- ON句で、ローのマッチングを行う方法を指定します。この例では、両方のテーブルに対してプライマリキーを使用します。
- 最初のWHEN句では、'Add'トランザクションが部品（part）テーブルのいずれのローとも一致しない場合に実行する操作を指定します。
- 2番目のWHEN句では、'Change'トランザクションが一致したときに実行する操作を指定します。この例では、descriptionカラムまたはpriceカラム、あるいはその両方のカラムのUPDATEを指定しています。
- 3番目のWHEN句では、一致する'Delete'トランザクションをDELETE操作に変換します。
- 4番目のWHEN句では、'Duplicate Add ignored.'というエラーを記録します。
- 5番目のWHEN句では、一致しない'Change'トランザクションをINSERTに変換します。
- 最後のWHEN句では、テーブルにない部品を'Delete'しようとする試みを単に無視します。
このMERGE文の初回実行では、最初のWHEN句が4回実行され、部品テーブルにデータが挿入されます。
part_number description price exceptions =========== =========== ===== ========== 1 widget 10.00 2 gizmo 20.00 3 thingie 30.00 4 bouncer 40.00
ここから面白くなります。6つの新しいトランザクションが残りのすべてのWHEN句を実行します。
TRUNCATE TABLE transaction; INSERT transaction VALUES ( 1, 'Change', NULL, 15.00 ); INSERT transaction VALUES ( 2, 'Change', 'gizmoid', NULL ); INSERT transaction VALUES ( 3, 'Delete', NULL, NULL ); INSERT transaction VALUES ( 4, 'Add', 'bouncer', 45.00 ); INSERT transaction VALUES ( 5, 'Change', 'gong', 55.00 ); INSERT transaction VALUES ( 6, 'Delete', NULL, NULL );
再びMERGEを実行すると、部品テーブルは次のようになります。
part_number description price exceptions =========== =========== ===== ========== 1 widget 15.00 2 gizmoid 20.00 4 bouncer 40.00 Duplicate Add ignored. 5 gong 55.00 Change accepted, new part inserted.
ただし、MERGE文にも1つ欠点があります。複数の入力ローで1つの出力ローを更新できないのです。これはSQL Anywhereの落ち度ではなく、例のロクでもない委員会が決定したことであり、この点はMERGE文についての最大の不満になることでしょう。「ANSI SQL/2003標準では、マージ操作中にソースオブジェクト内の複数のローがターゲットオブジェクト内のローを更新することが許されない」のです。MERGE文について2番目に要望が多いのは、おそらくWHEN句からプロシージャを呼び出す機能だと思われます。
