2-2-6 条件付きinsert(複数レコード)

SQLパズル

UniqTable2のCol1,Col2に、以下の3レコードで一意制約違反が発生しないレコードのみを追加する。
レコード1(1,2)
レコード2(3,4)
レコード3(5,6)
Table1のプライマリキーは、Col1とする。

データ作成スクリプト

create table UniqTable2(Col1 primary key,Col2) as
select 3,4 from dual;

SQL

insert into UniqTable2(Col1,Col2)
select val1,val2 from (select 1 as val1,2 as val2 from dual
                       union all select 3,4 from dual
                       union all select 5,6 from dual) a
where not exists(select 1 from UniqTable2 b
                  where b.Col1=a.val1);

解説

インラインビューで作成した集合の中で、
一意制約違反が発生しないレコードを、まとめてinsertしてます。

ただし、ファントムリードやアンリピータブルリードによる一意制約違反を完全に防ぐには、
for updateを使って、ロックをかける必要があります。


Oracle11gR2からは、IGNORE_ROW_ON_DUPKEY_INDEXヒントを使って、一意制約違反を完全に防げるようです。

INSERT文のIGNORE_ROW_ON_DUPKEY_INDEXヒント

INSERT INTO TARGET...SELECT...FROM SOURCEでは、
挿入する行の一意キーが既存の行と衝突することがあります。
IGNORE_ROW_ON_DUPKEY_INDEXにより、警告なしに衝突を無視して、衝突のない行を挿入できます。

---