2021年5月1日土曜日

SQLite 3 INSERT時にキーが重複していたら挿入しないようにする (do nothing)

概要

Primary keyやUNIQUE制約が重複するようなデータをinsertしようとすると、通常はエラーになる。 「on conflict ～ do nothing」構文を使えばエラーを出さずにキャンセルし、 重複していないレコードだけを挿入することが出来る。

一括でデータを登録したいけど重複行は無視したい、というような場合便利。

構文


insert into table_name (
    column1
    , column2
    , column3 ...
)
values (
    value1
    , value2
    , value3 ...
)
on conflict ( key_column1, key_column2 ...  )
do nothing
;

SQLite 3 でレコードがあれば更新、なければ新規挿入する (upsert)

実行例

実行環境
  • Windows 10
  • sqlite3 (3.35.4) Command-Line Shell
サンプルテーブル(product)
id
primary key 		name quantity remark
1 tomato 100
2 potato 120 bagging
3 pumpkin 50 cutted

まずはキーが重複してエラーが出る例。
「Error: UNIQUE constraint failed:～」となる。 



sqlite> insert into product (
   ...>    id
   ...>    , name
   ...>    , quantity
   ...>    , remark
   ...> )
   ...> values (
   ...>    1
   ...>    , 'cassava'
   ...>    , 100
   ...>    , ''
   ...> );
Error: UNIQUE constraint failed: product.id
sqlite>

「on conflict ～ do nothing 」を使うとエラーが出なくなり、 テーブルも元通りのまま。 


sqlite> insert into product (
   ...>    id
   ...>    , name
   ...>    , quantity
   ...>    , remark
   ...> )
   ...> values (
   ...>    1
   ...>    , 'cassava'
   ...>    , 100
   ...>    , ''
   ...> )
   ...> on conflict (id)
   ...> do nothing
   ...> ;
sqlite> select * from product;
┌────┬─────────┬──────────┬─────────┐
│ id │  name   │ quantity │ remark  │
├────┼─────────┼──────────┼─────────┤
│ 1  │ tomato  │ 100      │         │
│ 2  │ potato  │ 120      │ bagging │
│ 3  │ pumpkin │ 50       │ cutted  │
└────┴─────────┴──────────┴─────────┘

応用

「on conflict ～ do nothing」は複数のデータをinsertする際にも使うことができる。 例えば「insert～select」で他のテーブルからデータをコピーする場合、キー項目が重複していたデータを無視しつつ 重複しないデータのみをinsertすることが出来る。


insert into table_name_A (
    column1
    , column2
    , column3 ...
)
select
    column4
    , column5
    , column6 ...
from
    table_name_B
where true
on conflict ( key_column )
do nothing
;

参考サイト

Posted by at
Labels: , ,