SQLite 備忘録: on conflict

INSERTのまとめ

概要

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
;

参考サイト

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
INSERT
INSERT文に関する公式のドキュメント(英語)
https://www.sqlite.org/lang_insert.html

INSERTのまとめ

UPDATEのまとめ

概要

SQLite 3 でキーか重複するレコードがあれば更新、なければ新規挿入する方法。

具体的にはテーブルにレコードを新規挿入しようとして Primary key や Unique が重複するレコードが既に存在していた場合、新規挿入をやめて update に切り替えるための構文。

以下のような処理分岐をしてくれる。

重複するレコードがある場合	既存のレコードを「更新(update)」する、もしくは何もしない(nothing)
重複するレコードがない場合	そのまま「新規挿入(insert)」

似たような処理にreplaceがあるが、 replaceはレコード(行)を丸ごと入れ替える(delete/insert)のに対し、 upsertはカラムを指定してレコードを更新する。

SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

公式によると PostgreSQL の構文を利用しているとのこと。
Microsoft SQLServer や Oracle の「merge(マージ)」構文に近い使い方だが
mergeは重複の判定にUniqueカラム以外のものを指定できたりする。

実行例

「upsert」というコマンドがあるわけではなく、 on conflict文を使いキーが重複した場合の処理を指定することができる。

on conflict文は sqlite のバージョン3.24.0 から利用可能。

環境

Windows 10
sqlite3 (3.24.0) Command-Line Shell

サンプルテーブル(product)
id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	120
3	pumpkin	50	(Comment...)

重複していたら特定のカラムを更新する

以下は「id」カラムが重複していたら新規挿入をやめて、別の値で「quantity」カラムを更新する例。


insert into 
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;

結果
id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	200
3	pumpkin	50	(Comment...)

excluded テーブルを利用する

挿入しようとして、ユニークキーの重複エラーになった行は「excluded」という一時テーブルに登録される。 update の際にこのexcludedを利用できる。

以下は「id」カラムが重複していたら新規挿入をやめて、挿入しようとした値で「quantity」カラムを更新する例


insert into
    product (
        id
        , name     
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
)
on conflict(id)
do update
    set
        quantity = excluded.quantity
;

結果
id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	150
3	pumpkin	50	(Comment...)

重複していなければ、新規登録される

以下は「id」カラムが重複していないので、そのまま新規挿入される例。


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        4
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;

結果
id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	120
3	pumpkin	50	(Comment...)
4	potato	150

重複していた場合は何もしない、ということもできる

on conflictの後にdo nothingを記述すれば重複していた場合は何もしない、という処理に出来る。


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do nothing
;

結果
id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	120
3	pumpkin	50	(Comment...)

行を丸ごと入れ替えるだけであれば replace を使う。

参考URL

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
SQLite Query Language: upsert (英語)
公式のUPSERTに関するドキュメント
https://www.sqlite.org/lang_UPSERT.html

あさはか備忘録: SQLite3 Selectしたデータを他のテーブルに挿入
https://sfnovicenotes.blogspot.com/2019/07/sqlite3-select.html
あさはか備忘録: SQLite3 Selectした結果でUpdateする (update from)
https://sfnovicenotes.blogspot.com/2020/10/sqlite3-selectupdate-update-from.html
あさはか備忘録: SQLite3 SelectしたデータでUpdateする
https://sfnovicenotes.blogspot.com/2019/08/sqlite3-selectupdate.html

SQLite 備忘録

2021年5月1日土曜日

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

概要

構文

実行例