2019年7月2日火曜日

SQLite3でUpsertを実行する

概要

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

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

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

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

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

公式 によると 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

Posted by at
Labels: , , ,