UPSERT の書き方 - UPSERT の方言 (ON CONFLICT / ON DUPLICATE / MERGE)

存在すれば UPDATE、無ければ INSERT の UPSERT は RDBMS で記法が大きく異なる。PostgreSQL/MySQL/SQLite/SQL Server の比較

概念図

UPSERT の方言 (ON CONFLICT / ON DUPLICATE / MERGE) diagram

構文

sql

INSERT ... ON CONFLICT (key) DO UPDATE SET ...  /  MERGE INTO ...

サンプル

同じ「存在すれば更新、無ければ挿入」を 4 つの RDBMS で書いた例

sql

-- PostgreSQL / SQLite 3.24+
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'a@example.com')
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      email = EXCLUDED.email;

-- MySQL
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'a@example.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

-- SQL Server
MERGE INTO users AS tgt
USING (VALUES (1, 'Alice', 'a@example.com')) AS src (id, name, email)
  ON tgt.id = src.id
WHEN MATCHED THEN
  UPDATE SET name = src.name, email = src.email
WHEN NOT MATCHED THEN
  INSERT (id, name, email) VALUES (src.id, src.name, src.email);

UPSERT とは

UPSERT は Update + Insert の造語で、キーが既に存在すれば UPDATE、無ければ INSERT する操作です。自然キーでデータを同期する ETL や、冪等性が必要な API でよく使われます。

SQL 標準は MERGE 文を定めていますが、PostgreSQL や MySQL には別方言が普及しており、実務では「その DB ではどう書くか」を覚える必要があります。

RDBMS 別比較表

RDBMS	構文	参照する新値
PostgreSQL 9.5+	`INSERT ... ON CONFLICT (col) DO UPDATE`	`EXCLUDED.col`
SQLite 3.24+	`INSERT ... ON CONFLICT (col) DO UPDATE`	`excluded.col`
MySQL / MariaDB	`INSERT ... ON DUPLICATE KEY UPDATE`	`VALUES(col)` or `new.col` (8.0.20+)
SQL Server	`MERGE INTO ...`	`src.col` (エイリアス経由)
Oracle	`MERGE INTO ...`	`src.col` (エイリアス経由)

DO NOTHING / 条件付き更新

重複時に「何もしない」だけでよい場合、PostgreSQL/SQLite は ON CONFLICT ... DO NOTHING でシンプルに書けます。MySQL には専用構文は無いので INSERT IGNORE を使いますが、キー重複以外のエラー（NOT NULL 違反など）も握り潰すので注意。

条件付きで更新したい場合は、PostgreSQL は ON CONFLICT ... DO UPDATE ... WHERE、SQL Server は MERGE ... WHEN MATCHED AND <condition> が使えます。

sql

-- PostgreSQL: より新しいレコードだけ更新
INSERT INTO events (id, updated_at, payload)
VALUES (1, NOW(), '...')
ON CONFLICT (id) DO UPDATE
  SET updated_at = EXCLUDED.updated_at,
      payload = EXCLUDED.payload
  WHERE events.updated_at < EXCLUDED.updated_at;

落とし穴と注意点

MySQL の ON DUPLICATE KEY: 一意制約が複数あると、どの一意制約がヒットしても更新が走る。意図しない列で突き当たるとデータ破壊の原因に
AUTO_INCREMENT の飛び: MySQL の ON DUPLICATE KEY UPDATE は更新だけでも AUTO_INCREMENT を消費し、ID に穴が空く
SQL Server の MERGE: 同時実行時に race condition や重複エラーの報告例が多い。HOLDLOCK (SERIALIZABLE) ヒントや、素直に IF EXISTS ... UPDATE ELSE INSERT を推奨する声もある
SQLite の ON CONFLICT: 3.24 未満では使えないため、古い環境では INSERT OR REPLACE を使うが、こちらは既存行を DELETE してから INSERTするので外部キーカスケードや連番に副作用あり