SQLite の UPSERT：データがあれば更新、なければ挿入
実務でよくあるパターンです。行を挿入したいけれど、同じキーの行がすでに存在しているなら更新したい。UPSERT がない時代は、まず SELECT で確認してから INSERT か UPDATE に分岐させる必要があり、ラウンドトリップが 2 回発生するうえ、その間に競合状態が起きる可能性もありました。
SQLite の UPSERT なら、これを 1 つの文で済ませられます。
初回実行では行が新しく挿入されます。同じ sku で価格だけ変えて再実行すると、既存の行がその場で更新されます。重複行もエラーも発生しません。
ON CONFLICT 構文の構造
基本の形はこうなります:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
押さえるべきポイントは3つです。
conflict_target—UNIQUE制約やPRIMARY KEYが付いていて、衝突が起きそうなカラム（複数可）を指定します。SQLite はこの情報をもとに、どのインデックスを監視するかを判断します。DO UPDATE SET ...— 衝突が発生したときに、既存の行をどう更新するかを書きます。何もせず黙って通したい場合はDO NOTHINGでもOKです。WHERE句（任意） — UPDATE を実際に走らせる条件をさらに絞り込みたいときに使います。
conflict target には、実在する UNIQUE 制約と一致するカラムを指定する必要があります。たとえば price カラムにユニーク制約が付いていなければ ON CONFLICT(price) はコンパイルが通りません。SQLite 側に「何をもって衝突とみなすか」の判断材料がないからです。
DO NOTHING: 既に存在するならスキップする
シンプルな方の使い方です。初期データの投入やイベントログの記録など、重複は黙ってスルーしてほしいケースで重宝します。
2 回目の INSERT は同じ event_id にぶつかるので、普通なら UNIQUE constraint failed エラーになります。ところが DO NOTHING を付けておくと、SQLite は何事もなかったかのようにスキップしてくれます。例外も出ませんし、行も増えません。
いわゆる「冪等な INSERT」というやつで、これまで INSERT OR IGNORE を使っていたケースと同じ用途です。ただ UPSERT の DO NOTHING のほうが、WHERE 句や RETURNING 句と組み合わせたときの相性がよく、書きやすくなります。
excluded 疑似テーブルとは
競合が発生した瞬間、扱う行が 2 つになります。テーブルにすでに入っている行と、これから入れようとして弾かれた行です。SQLite ではこの 2 つをきちんと区別して参照できるようになっています。
- 列名をそのまま書く(
price、nameなど)と、既存の行を指します。 excluded.columnと書くと、新しく入れようとした行(つまり弾かれた側)を指します。
quantity = quantity + excluded.quantity は「既存の quantity に新しい quantity を足す」という意味です。2 回 INSERT した後、A-100 の quantity は 8 になります。このように既存行に値を積み上げていくパターンは、UPSERT の中でもとくに便利な使い方のひとつです。
WHERE で条件付き UPSERT を実現する
末尾に WHERE を付けると、条件を満たさないときは更新をスキップできます。この条件は既存行に対して評価され、excluded.* で新しく入ってこようとしている行も参照できます。
新しい行のほうが updated_at が古いので、WHERE が false になり更新はスキップされます。既存行の新しい価格はそのまま残ります。日付を入れ替えれば更新が走ります。これは「より新しいデータでだけ上書きする」という定番のパターンです。
複数行をまとめて UPSERT する
VALUES には複数行を書けて、ON CONFLICT は各行ごとに独立して評価されます。
A-100 は競合するので更新され、A-200 と A-300 は新規としてそのまま挿入されます。1 つの SQL で挿入と更新が混在した結果が得られるわけです。外部ソースからまとめてレコードを同期したいときに、すっきり書ける書き方ですね。
UPSERT と INSERT OR REPLACE の違い
INSERT OR REPLACE も一見同じ動きをするように見えますが、中身はまったく別物です。
notes が消えてしまいました。INSERT OR REPLACE は元の行（id=1）を一度削除してから新しい行を挿入する動作なので、INSERT 文で指定しなかったカラムはすべて NULL か既定値に戻ってしまいます。さらに DELETE トリガーが発火し、ON DELETE の外部キー制約もカスケードで波及します。
一方、UPSERT なら行はそのまま残ります。
notes はそのまま残ります。変わったのは SET で指定したカラムだけです。基本は UPSERT を使い、本当に「削除してから入れ直す」挙動が欲しいときだけ INSERT OR REPLACE を選びましょう。
複数の競合ターゲットを指定する
1 行が複数の制約に引っかかる可能性がある場合は、ON CONFLICT 句をいくつも連ねることができます。
最初に違反した制約が勝ち、その分岐の DO UPDATE が実行されます。実際のところ、ほとんどのテーブルには競合対象が1つしかありません（主キーか、単一の UNIQUE カラム）。複数の ON CONFLICT 句を書く場面はそう多くないでしょう。
よくあるハマりどころ
UPSERT を使うときに引っかかりやすいポイントをいくつか挙げておきます。
- 対応する UNIQUE インデックスがないと UPSERT は使えない。
ON CONFLICT(col)のcolはPRIMARY KEYかUNIQUE制約を持っている必要があります。そうでないと SQLite は "no such constraint" というエラーを返します。 - 競合が起きなければ
DO UPDATEは走らない。 あくまで INSERT の 代替 であって、追加で動く処理ではありません。そのキーを初めて入れるときは普通に INSERT されるだけです。 excludedは読み取り専用。 参照はできても書き込みはできません。SETの対象は常に既存行のカラムです。INTEGER PRIMARY KEYの自動採番。 id を指定しないと毎回新しい値が振られるので、そもそも競合しようがありません。UPSERT が意味を持つのは、呼び出し側が決まった値を渡すカラムで競合が起きるケースです。
次は RETURNING
UPSERT 単体では、どの行が INSERT されてどの行が UPDATE されたのか、最終的にどんな値になったのかは分かりません。それを知りたいときに使うのが RETURNING 句です。同じ文の中で影響を受けた行をそのまま返してくれるので、後追いで SELECT を打つ必要はありません。次はこれを見ていきましょう。
よくある質問
SQLiteのUPSERTって何ですか?
ひとことで言うと、UNIQUEやPRIMARY KEY制約に引っかかったときだけINSERTをUPDATE(または何もしない)に切り替えてくれる仕組みです。書き方は INSERT ... ON CONFLICT(カラム名) DO UPDATE SET ... か DO NOTHING。SQLite 3.24.0(2018年リリース)から使えます。
UPSERTで出てくる excluded テーブルって何者?
excludedは「INSERTしようとしたけど弾かれた行」を一時的に持っている疑似テーブルです。DO UPDATE SET ...の中では、既存の行はカラム名そのまま、これからINSERTしようとした側の値はexcluded.カラム名で参照します。たとえば SET price = excluded.price と書けば、「新しく入れようとした price で上書きして」という意味になります。
INSERT OR REPLACE と UPSERT は何が違うの?
INSERT OR REPLACEは競合した行を一度DELETEしてから新しい行をINSERTし直します。なのでDELETEトリガが発火し、ON DELETE CASCADEがついた外部キーは連鎖的に消え、SETしていないカラムはデフォルト値に戻ってしまいます。一方UPSERTは既存行をその場で更新するので、SETで指定したカラムだけが変わります。基本はUPSERTを選んでおき、本当に削除→再挿入したいときだけINSERT OR REPLACEを使うのが安全です。
UPSERTで複数行をまとめて入れられますか?
もちろん可能です。INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... という書き方で動きます。各行ごとに競合チェックが行われ、DO UPDATE内のexcludedは「いま競合を起こしている行」を指します。
