#背景と状況
テーブルにマスターデータを追加する際、すでに同じ主キーのレコードが存在する場合があります。アプリケーションの都合で先行でマスター登録している場合があるので、先に登録したものを正しいものとして扱います。よって、登録されていないマスターだけを登録することになります。
#やりたいこと
insertしようとするレコードのうち、対象テーブルに存在しないレコードのみをinsertしたい。具体的には主キーが重複しないもののみinsertしたい。単純にinsertしようとすると重複キーでエラーになります。
※IBM Db2 V11.5 Windowsで確認していますが他のDBMSでもできるはずです。
#insert対象のテーブル
主キーを持つテーブルを用意します。
マスターテーブル mst_tbl
<-主キー->
MST_ID MST_TEXT MST_PRICE MST_DATE
----------- --------------- ----------- ----------
1 リンゴ 200 2020-01-01
2 バナナ 150 2020-01-01
3 イチゴ 650 2020-01-01
4 ミカン 300 2020-01-01
5 スイカ 850 2020-01-01
#単純にinsertした場合
複数レコードをinsertするSQLを用意します。2レコード（ミカンとスイカ）が対象テーブルと主キーが重複しています。
insert.sql
insert into mst_tbl( mst_id, mst_text, mst_price, mst_date ) values
( 4, 'ミカン', 300, current date) ,
( 5, 'スイカ', 850, current date) ,
( 6, 'メロン', 900, current date) ,
( 7, 'ライム', 300, current date) ;
主キーの重複エラーで失敗します。
実行結果
C:\_work>db2 -tvmf insert.sql
insert into mst_tbl( mst_id, mst_text, mst_price, mst_date ) values ( 4, 'ミカン', 300, current date) , ( 5, 'スイカ', 850, current date) , ( 6, 'メロン', 900, current date) , ( 7, 'ライム', 300, current date)
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0803N INSERT ステートメント、UPDATE ステートメントの 1 つ以上の値、および
DELETE ステートメントが原因で発生した外部キーの更新は無効です。これは、"1"
で識別される主キー、ユニーク制約、またはユニーク索引が表 "DB2ADMIN.MST_TBL"
が索引キーに対して重複する値を持つことを制限しているためです。 SQLSTATE=23505
#MERGEを使う方法
MERGE文で主キーがマッチしない場合のみ、insertするようにSQLを記述します。
merge.sql
merge into mst_tbl as org
using ( values
( 4, 'ミカン', 300, current date) ,
( 5, 'スイカ', 850, current date) ,
( 6, 'メロン', 900, current date) ,
( 7, 'ライム', 300, current date)
) as new ( mst_id, mst_text, mst_price, mst_date )
on org.mst_id = new.mst_id
when not matched then
insert ( mst_id, mst_text, mst_price, mst_date )
values ( mst_id, mst_text, mst_price, mst_date ) ;
主キーが重複しないレコード（メロンとライム）がinsertされます。
実行結果
C:\_work>db2 -tvmf merge.sql
merge into mst_tbl as org using ( values ( 4, 'ミカン', 300, current date) , ( 5, 'スイカ', 850, current date) , ( 6, 'メロン', 900, current date) , ( 7, 'ライム', 300, current date) ) as new ( mst_id, mst_text, mst_price, mst_date ) on org.mst_id = new.mst_id when not matched then insert ( mst_id, mst_text, mst_price, mst_date ) values ( mst_id, mst_text, mst_price, mst_date )
影響を受ける行数: 2
DB20000I SQL コマンドが正常に完了しました。
C:\_work>db2 select * from MST_TBL
MST_ID MST_TEXT MST_PRICE MST_DATE
----------- --------------- ----------- ----------
1 リンゴ 200 2020-01-01
2 バナナ 150 2020-01-01
3 イチゴ 650 2020-01-01
4 ミカン 300 2020-01-01 <-- 元からあるレコード
5 スイカ 850 2020-01-01 <-- 元からあるレコード
6 メロン 900 2021-08-11 <-- insertしたレコード
7 ライム 300 2021-08-11 <-- insertしたレコード
7 レコードが選択されました。
#NOT EXISTSを使う方法
NOT EXISTSで主キーが一致しないレコードを選択します。
insert_not_exists.sql
insert into mst_tbl
select * from ( values
( 4, 'ミカン', 300, current date) ,
( 5, 'スイカ', 850, current date) ,
( 6, 'メロン', 900, current date) ,
( 7, 'ライム', 300, current date)
) as new ( mst_id, mst_text, mst_price, mst_date )
where not exists (select * from mst_tbl where mst_id = new.mst_id) ;
MERGE文と同様に主キーが重複しないレコード（メロンとライム）がinsertされます。
実行結果
C:\_work>db2 -tvmf insert_not_exists.sql
insert into mst_tbl select new.* from ( values ( 4, 'ミカン', 300, current date) , ( 5, 'スイカ', 850, current date) , ( 6, 'メロン', 900, current date) , ( 7, 'ライム', 300, current date) ) as new ( mst_id, mst_text, mst_price, mst_date ) where not exists (select * from mst_tbl where mst_id = new.mst_id)
影響を受ける行数: 2
DB20000I SQL コマンドが正常に完了しました。
C:\_work>db2 select * from MST_TBL
MST_ID MST_TEXT MST_PRICE MST_DATE
----------- --------------- ----------- ----------
1 リンゴ 200 2020-01-01
2 バナナ 150 2020-01-01
3 イチゴ 650 2020-01-01
4 ミカン 300 2020-01-01 <-- 元からあるレコード
5 スイカ 850 2020-01-01 <-- 元からあるレコード
6 メロン 900 2021-08-11 <-- insertしたレコード
7 ライム 300 2021-08-11 <-- insertしたレコード
7 レコードが選択されました。
#まとめ
- NOT EXISTSの方が記述が簡潔
- MERGE文は主キーが一致した場合のupdateが追加可能
- いずれも存在する主キーを確認してからそれ以外のレコードをinsert