あれば更新・なければ追加 - MERGE / UPSERT

存在すれば UPDATE、なければ INSERT。標準 MERGE と PostgreSQL / MySQL の方言

概念図

構文

sql

INSERT ... ON CONFLICT (key) DO UPDATE SET ... (PostgreSQL)

サンプル

存在すれば更新、なければ挿入する UPSERT の典型例

sql

-- PostgreSQL: 同じ email があれば name を上書き
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1;

UPSERT とは

「存在すれば UPDATE、なければ INSERT」を 1 文で実行する操作を UPSERT（または MERGE）と呼びます。バッチ処理の冪等化、マスタ同期、カウンター更新などで多用されます。

素朴に SELECT → 分岐 → INSERT or UPDATE と書くと、レースコンディションで ユニーク制約違反か 重複挿入が発生します。UPSERT 構文はこれを原子的に解決します。

標準 SQL の MERGE

SQL 標準では MERGE INTO ... USING ... ON ... WHEN MATCHED / WHEN NOT MATCHED 構文が定義されています。SQL Server / Oracle / DB2 が古くから対応、PostgreSQL も 15 から対応しました。

MySQL / SQLite は標準 MERGE 未対応で、方言の UPSERT 構文を使います。UPSERT / MERGE の書き方差分は RDBMS 方言早見表を参照。

sql

-- 標準 MERGE (PostgreSQL 15+, SQL Server)
MERGE INTO users AS t
USING (VALUES ('alice@example.com', 'Alice')) AS s(email, name)
ON t.email = s.email
WHEN MATCHED THEN
  UPDATE SET name = s.name
WHEN NOT MATCHED THEN
  INSERT (email, name) VALUES (s.email, s.name);

PostgreSQL の ON CONFLICT

PostgreSQL では INSERT ... ON CONFLICT (key) DO UPDATE SET ... が定番の UPSERT 構文です。標準 MERGE より簡潔で、EXCLUDED 擬似テーブルで「挿入しようとした値」を参照できます。

「何もしない」なら ON CONFLICT DO NOTHING。ユニーク制約違反を無視したいときに便利です。

sql

INSERT INTO page_views (page, views)
VALUES ('/home', 1)
ON CONFLICT (page)
DO UPDATE SET views = page_views.views + EXCLUDED.views;

-- 衝突は無視
INSERT INTO tags (name) VALUES ('sql')
ON CONFLICT (name) DO NOTHING;

MySQL の ON DUPLICATE KEY UPDATE

MySQL / MariaDB は INSERT ... ON DUPLICATE KEY UPDATE を使います。PRIMARY KEY または UNIQUE 制約違反が発生したときに UPDATE 句が実行されます。

新しく挿入しようとした値は VALUES(col)（MySQL 8.0.20 以降は new.col エイリアス推奨）で参照します。

sql

-- MySQL
INSERT INTO page_views (page, views)
VALUES ('/home', 1) AS new
ON DUPLICATE KEY UPDATE
  views = page_views.views + new.views;

-- SQLite (PostgreSQL と同じ ON CONFLICT が使える)
INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1;

落とし穴: どのキーで衝突するか曖昧

複数ユニーク制約がある表: MySQL の ON DUPLICATE KEY UPDATE は「いずれかの一意制約に触れたら」発火する。どの制約で衝突したのか区別できず、意図しない UPDATE が走る
対策: PostgreSQL の ON CONFLICT (col) のように衝突対象を明示する書き方を好む。MySQL なら一意制約を必要最小限にする
自動採番との罠: UPSERT で衝突して UPDATE になっても AUTO_INCREMENT は消費される（歯抜けが発生）
SQL Server の MERGE: 同時実行時に Unique Key Violation を起こすレースコンディションが知られている。HOLDLOCK ヒントを付けるのが推奨