UPSERT ― 「あれば更新、なければ追加」

導入

「そのIDの行がすでにあれば更新、なければ新規に追加」――この「あれば更新・なければ挿入」は、在庫の取り込みや設定の保存など、実務でものすごくよく出てきます。素直に書くと「まず SELECT して、あるか調べて、INSERT か UPDATE を選ぶ」と3手かかりますが、これを1文でやるのが UPSERT（update + insert）です。

説明

SQLite では INSERT ... ON CONFLICT(...) DO UPDATE で書きます。「主キーや UNIQUE 制約とぶつかったら（＝すでに同じ行がいたら）、代わりに UPDATE する」という意味です。

flowchart TB
    I["INSERT を試みる"] --> Q{"同じ id が<br/>すでにある?"}
    Q -->|"ない"| A["そのまま挿入される"]
    Q -->|"ある（衝突）"| U["ON CONFLICT で指定した<br/>UPDATE が実行される"]

INSERT INTO users (id, name, age, city) VALUES (1, '佐藤', 28, '那覇')
ON CONFLICT(id) DO UPDATE SET city = excluded.city;

ポイントは excluded という特別な名前です。これは「いま挿入しようとした（が弾かれた）値」を指します。上の例なら excluded.city は '那覇'。つまり「id=1 がすでにあるので、その人の city を、挿入しようとした '那覇' に更新する」という動きになります。id=1 は既存なので、結果は UPDATE。存在しない id を指定すれば、ふつうに INSERT されます。

DO NOTHING を使うと「衝突したら何もしない（重複はそっと無視）」にもできます: INSERT ... ON CONFLICT(id) DO NOTHING;。

やってみよう

初期表示の UPSERT を実行し、続く SELECT で佐藤さん（id=1）の city が「那覇」に更新されたことを確認しましょう（id=1 は既存なので更新側が動きます）。次に VALUES の id を存在しない 99 に変えて実行すると、今度は衝突が起きず新規追加されることを見比べてください。

演習

UPSERT を使って、categories の id=1 の行について「同じ id があれば name を '食品・飲料' に更新する」1文を書いてください（ON CONFLICT(id) DO UPDATE を使い、更新値は excluded.name を使います）。