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

概要

replace (insert or replace) 構文を使う方法のメモ。

replace を使うと Primary key や Unique で 重複するレコードを挿入しようとした場合、 以下のような処理分岐をしてくれる。

重複するレコードがある場合	既存のレコードを「削除(delete)」 してから 「新規挿入(insert)」
重複するレコードがない場合	そのまま 「新規挿入(insert)」

注意

replaceで重複したキーがあった場合の処理は「削除(delete)」「新規挿入(insert)」であり、 「更新(update)」ではない。 カラムを指定しなければデフォルト値が入るし、NOT NULL制約にもひっかかる。

カラムを指定して「更新(update)」したい場合は replaceではなくupsertを使用する。
SQLite3 で upsert する

環境

• Windows 10
• SQLite3 (3.24.0) Command-Line Shell

replace 構文の例

サンプルテーブル(product)

id primary key autoincrement	name not null	price default 0	category
1	tomato	100
2	potato	120
3	pumpkin	50	(Comment...)

replace(insert or replace) の実行例

以下はidが重複するデータと重複しないデータを挿入するSQL。 「insert or replace」の部分は「replace」だけでもOK。

insert or replace into product (
    id
    , name
    , quantity
    , remark
)
values (
    2
    , 'potato'
    , 150
    , ''
),(
    4
    , 'carrot'
    , 100      
    , 'Prefecture: XX'
);
                    

結果

id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	150
3	pumpkin	50	(Comment...)
4	carrot	100	(Prefecture : XX)

カラムを指定しないとデフォルト値になる

delete > insert であり、updateではないことを留意する。

replace into product (
    id
    , name
)
values (
    2
    , 'potato'
);                        
                    

結果

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

not null 制約にも引っかかる

replace into product (
    id
    , quantity
)
values (
    2
    , 200
);
                    

結果

Error: NOT NULL constraint failed: product.name
                    

カラムを指定してUPDATEする場合はUPSERTを利用する。
SQLite 3 で upsert する

参考・関連 URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: REPLACE(英語)
  公式のREPLACE構文に関するドキュメント
  https://www.sqlite.org/lang_replace.html
• SQL99 の replace に関する情報 (英語)
  MariaDBのサイトですが SQLite にも共通する内容です。
  https://mariadb.com/kb/en/library/replace/