SQL UPSERTの書き方｜PostgreSQL・MySQL・MERGE構文比較表

UPSERTとは、「同じキーの行があれば更新（UPDATE）し、なければ挿入（INSERT）する」処理を1つのSQL文で行う書き方です。 標準SQLの UPSERT という命令はなく、PostgreSQL は INSERT … ON CONFLICT、MySQL は INSERT … ON DUPLICATE KEY UPDATE、SQL Server・Oracle・PostgreSQL 15以降は MERGE と、DBごとに構文が違います。どの構文も 判定の基準は一意制約（PRIMARY KEY / UNIQUE） です。

この記事では、PostgreSQL 16.13 と MySQL 8.0.46 に同じ在庫テーブルを作り、影響行数（MySQL の 1 / 2 / 0）、連番の欠番、REPLACE で別の列の値が消える様子、2つのセッションから同時に UPSERT したときの動き を実際に実行して確かめました。MySQL 8.0.19 以降の AS new 行エイリアス（VALUES() 関数の後継）や、PostgreSQL の EXCLUDED・WHERE・RETURNING、MERGE との使い分けも、コピペできるSQLと実行結果つきで解説します。

UPSERT構文の早見表

PostgreSQL・SQLite は「どの一意制約で判定するか」を指定し、MySQL は「すべての一意キー」で判定する という違いが、複数の一意キーがあるテーブルでの動きの差になります。

検証に使ったテーブル（コピペ用）

在庫（備品）テーブルを、品番 sku に一意制約を付けて作ります。

-- PostgreSQL 16
CREATE TABLE stock (
  id         serial PRIMARY KEY,
  sku        text NOT NULL UNIQUE,
  name       text NOT NULL,
  qty        int  NOT NULL DEFAULT 0,
  note       text,
  updated_at timestamp NOT NULL DEFAULT now()
);
INSERT INTO stock (sku, name, qty, note)
VALUES ('A-001','ボールペン',10,'棚1'), ('A-002','ノート',5,'棚2');

-- MySQL 8.0
CREATE TABLE stock (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  sku        VARCHAR(20) NOT NULL UNIQUE,
  name       VARCHAR(50) NOT NULL,
  qty        INT NOT NULL DEFAULT 0,
  note       VARCHAR(50),
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO stock (sku, name, qty, note)
VALUES ('A-001','ボールペン',10,'棚1'), ('A-002','ノート',5,'棚2');

PostgreSQLのUPSERT：ON CONFLICT

あれば更新・なければ挿入（DO UPDATE）

INSERT INTO stock (sku, name, qty) VALUES ('A-001','ボールペン黒',30)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name, qty = EXCLUDED.qty, updated_at = now()
RETURNING id, sku, name, qty, note, (xmax = 0) AS inserted;

実行結果（PostgreSQL 16）:

 id |  sku  |     name     | qty | note | inserted 
----+-------+--------------+-----+------+----------
  1 | A-001 | ボールペン黒 |  30 | 棚1  | f
(1 row)

INSERT 0 1

EXCLUDED は「挿入しようとして衝突した行」を表す特別な表名 です。SET に書かなかった note は元の値（棚1）のまま残ります。存在しない A-003 で同じSQLを実行すると挿入になり、inserted が t になりました。

 id |  sku  |   name   | qty | inserted 
----+-------+----------+-----+----------
  4 | A-003 | 消しゴム |   7 | t
(1 row)

(xmax = 0) は「この文で挿入された行か」を見分けるためによく使われる書き方ですが、PostgreSQL の内部列に依存した方法でドキュメント上の正式な機能ではありません。必要な場面に絞って使ってください。

既存の値に加算する

テーブル名（または別名）で既存の行、EXCLUDED で新しい値を参照できます。

INSERT INTO stock (sku, name, qty) VALUES ('A-002','ノート',3)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING sku, qty;

  sku  | qty 
-------+-----
 A-002 |   8
(1 row)

あれば何もしない（DO NOTHING）

INSERT INTO stock (sku, name, qty) VALUES ('A-002','ノート',99)
ON CONFLICT (sku) DO NOTHING
RETURNING *;

 id | sku | name | qty | note | updated_at 
----+-----+------+-----+------+------------
(0 rows)

INSERT 0 0

DO NOTHING で何もしなかった行は RETURNING にも出ません。 既存行のIDも欲しいときは、続けて SELECT するか、DO UPDATE SET sku = EXCLUDED.sku のような「値の変わらない更新」で行を返す方法があります（後者は行を書き換えるため、更新コストとトリガー発火が発生します）。

値が変わるときだけ更新する（DO UPDATE … WHERE）

INSERT INTO stock (sku, name, qty)
VALUES ('A-001','ボールペン黒',30), ('A-003','消しゴム',8)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name, qty = EXCLUDED.qty, updated_at = now()
  WHERE (stock.name, stock.qty) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.qty)
RETURNING sku, qty;

  sku  | qty 
-------+-----
 A-003 |   8
(1 row)

INSERT 0 1

A-001 は値が同じなので更新されず、updated_at も変わりません。IS DISTINCT FROM は NULL 同士を「等しい」と扱う比較 なので、NULL を含む列の差分判定にも使えます。

制約名で指定する／対象を省略する

-- 制約名で指定
INSERT INTO stock (sku, name, qty) VALUES ('A-003','消しゴム',9)
ON CONFLICT ON CONSTRAINT stock_sku_key DO UPDATE SET qty = EXCLUDED.qty;

-- 対象を省略：どの一意制約に当たっても何もしない（DO NOTHING のみ可）
INSERT INTO stock (id, sku, name, qty) VALUES (1,'A-100','クリップ',1)
ON CONFLICT DO NOTHING;

2つ目は id=1 が主キーで衝突しますが、INSERT 0 0 で正常終了しました。

複数行を一括でUPSERTする

配列から一括で流し込むときは unnest が便利です。

INSERT INTO stock (sku, name, qty)
SELECT * FROM unnest(
  ARRAY['A-001','A-007'],
  ARRAY['ボールペン黒','マーカー'],
  ARRAY[30, 6]
)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
RETURNING sku, qty, CASE WHEN xmax = 0 THEN 'insert' ELSE 'update' END AS action;

  sku  | qty | action 
-------+-----+--------
 A-001 |  30 | update
 A-007 |   6 | insert
(2 rows)

アプリから送るときも、VALUES (…), (…), … の複数行INSERTか、この形で 1文にまとめると往復回数が減ります。ただし次の「同じキーを1文で2回」に注意してください。

MySQLのUPSERT：ON DUPLICATE KEY UPDATE

8.0.19以降の行エイリアス（AS new）

INSERT INTO stock (sku, name, qty) VALUES ('A-001','ボールペン黒',30) AS new
ON DUPLICATE KEY UPDATE name = new.name, qty = new.qty;
SELECT ROW_COUNT() AS affected;

affected
2

AS new で 挿入しようとした行に別名を付け、new.col で参照 します。new は任意の名前で、AS new(s, n, q) のように列の別名も付けられます。

VALUES() 関数は非推奨（警告の実例）

以前の書き方 VALUES(col) も動きますが、MySQL 8.0.20 以降は非推奨で、警告が出ます。

INSERT INTO stock (sku, name, qty) VALUES ('A-002','ノート',1)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SHOW WARNINGS;

Level   Code    Message
Warning 1287    'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

新しく書くなら AS new ＋ new.col に統一 しておくと、将来のバージョンでもそのまま動きます。

影響行数は 1 / 2 / 0 になる

MySQL の UPSERT は、ROW_COUNT()（やドライバの affected rows）が 挿入なら1、更新なら2、値が変わらなければ0 になります。同じテーブルで順に実行した結果です。

一括UPSERTでは合計値しか返らないため、「何件挿入して何件更新したか」を正確に知りたいときは PostgreSQL の RETURNING のような仕組みがありません。 件数を区別したい場合は、事前に存在する行を SELECT しておくなどの工夫が必要です。なお、接続時に CLIENT_FOUND_ROWS フラグを付けると、値が変わらない行も1として数えられます（MySQL リファレンスマニュアル「INSERT … ON DUPLICATE KEY UPDATE」より。本記事では未検証）。

AUTO_INCREMENTの欠番

UPSERT で更新になった行や、INSERT IGNORE で無視された行でも、AUTO_INCREMENT の番号は消費されます。上の表の操作をした後に普通の INSERT をすると、次のようになりました。

id  sku name    qty note
1   A-001   ボールペン黒  31  棚1
2   A-002   ノート 9   棚2
5   A-003   消しゴム    7   NULL
8   A-004   付箋  2   NULL
12  A-005   クリップ    4   NULL

行は5件ですが、id は 12 まで進んでいます（innodb_autoinc_lock_mode は8.0の既定値 2）。PostgreSQL の serial も同じで、ON CONFLICT で更新・無視になった回数ぶんシーケンスが進み、5回のUPSERTの後に挿入した行の id は 12 でした。連番は「一意な値」として使い、「件数」や「抜けのない番号」としては使わない 設計にしておくと、どちらのDBでも安心です。

INSERT IGNORE と REPLACE の違い

INSERT IGNORE：重複行を飛ばす

INSERT IGNORE INTO stock (sku, name, qty) VALUES ('A-002','ノート',99);
SELECT ROW_COUNT() AS affected;

affected
0

INSERT IGNORE は重複キーだけでなく、型変換や NOT NULL などのエラーも警告に変えて処理を続けます。 重複だけを飛ばしたいなら、ON DUPLICATE KEY UPDATE id = id のように「何も変えない更新」を書く方法もあります。

REPLACE：削除してから挿入する

REPLACE INTO stock (sku, name, qty) VALUES ('A-002','ノートB5',12);
SELECT ROW_COUNT() AS affected;

affected
2

実行前と後の A-002 の行:

-- 実行前
id  sku name    qty note
2   A-002   ノート 9   棚2
-- 実行後
id  sku name    qty note
13  A-002   ノートB5   12  NULL

REPLACE は「既存行を DELETE → 新しい行を INSERT」 なので、id が 2 から 13 に変わり、指定しなかった note（棚2）は NULL になりました。影響行数の 2 は「削除1＋挿入1」です。ほかのテーブルから id を外部キーで参照している場合や、指定しない列を残したい場合は、ON DUPLICATE KEY UPDATE を使う と既存行がそのまま更新されます。

複数の一意キーがあるときの挙動

品番 sku と JANコード jan の両方が一意のテーブルで、1行目と sku が、2行目と jan がぶつかる行 を入れてみます。

-- MySQL 8.0
CREATE TABLE item2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(10) UNIQUE, jan VARCHAR(13) UNIQUE, qty INT
);
INSERT INTO item2 (sku,jan,qty) VALUES
  ('A-001','4900000000011',1), ('A-002','4900000000028',1);

INSERT INTO item2 (sku,jan,qty) VALUES ('A-001','4900000000028',9) AS new
ON DUPLICATE KEY UPDATE qty = new.qty;

affected
2

結果のテーブル:

id  sku jan qty
1   A-001   4900000000011   9
2   A-002   4900000000028   1

更新されたのは1行（id=1）だけ です。MySQL のマニュアルにも「複数の一意インデックスに一致する場合は1行だけが更新される」とあり、複数の一意キーがあるテーブルでの ON DUPLICATE KEY UPDATE は結果が読みにくくなります。同じ状況で REPLACE を使うと、ぶつかった2行がどちらも削除され1行が挿入 されました。

affected
3
id  sku jan qty
4   A-001   4900000000028   5

PostgreSQL は判定する制約を指定するため、ON CONFLICT (sku) と書いて別の一意制約（ここでは主キー）に当たると、UPSERT にならずエラーになります。

INSERT INTO stock (id, sku, name, qty) VALUES (1,'A-100','クリップ',1)
ON CONFLICT (sku) DO NOTHING;

ERROR:  duplicate key value violates unique constraint "stock_pkey"
DETAIL:  Key (id)=(1) already exists.

UPSERT するテーブルは、判定に使う一意キーを1つに決めておく と、どのDBでも結果が読みやすくなります。

MERGEの書き方（PostgreSQL 15以降・SQL Server・Oracle）

MERGE は「元データ（USING）と対象テーブルを ON 条件で突き合わせ、一致・不一致ごとに処理を書く」標準SQLの構文です。

-- PostgreSQL 16 で実行
MERGE INTO stock s
USING (VALUES ('A-002','ノート',20), ('A-006','ホチキス',2)) AS v(sku, name, qty)
ON s.sku = v.sku
WHEN MATCHED THEN
  UPDATE SET qty = v.qty, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (sku, name, qty) VALUES (v.sku, v.name, v.qty);

MERGE 2

A-002 は更新、A-006 は挿入されました。MERGE は ON 条件で突き合わせるため、一意制約がなくても書けます（その代わり、ON に一致する行が対象側に複数あるとエラーになります）。

PostgreSQL 16 では MERGE に RETURNING が使えない

MERGE INTO stock s USING (VALUES ('A-002','ノート',21)) AS v(sku, name, qty)
ON s.sku = v.sku
WHEN MATCHED THEN UPDATE SET qty = v.qty
RETURNING *;

ERROR:  syntax error at or near "RETURNING"

PostgreSQL 17 のリリースノートでは、MERGE に RETURNING と、どの操作が行われたかを返す merge_action()、対象側にしかない行を処理する WHEN NOT MATCHED BY SOURCE、更新可能ビューへの MERGE が追加されています。

-- PostgreSQL 17 以降の書き方（本記事の環境はPG16のため未実行）
MERGE INTO stock s
USING new_stock v ON s.sku = v.sku
WHEN MATCHED THEN UPDATE SET qty = v.qty
WHEN NOT MATCHED THEN INSERT (sku, name, qty) VALUES (v.sku, v.name, v.qty)
WHEN NOT MATCHED BY SOURCE THEN DELETE
RETURNING merge_action(), s.sku, s.qty;

SQL Server・Oracle の MERGE（構文のみ・未実行）

以下は各DBの公式ドキュメントに沿った構文例で、本記事の環境では実行していません。

-- SQL Server（文末のセミコロンが必須）
MERGE INTO stock AS s
USING (VALUES ('A-002', N'ノート', 20)) AS v(sku, name, qty)
ON s.sku = v.sku
WHEN MATCHED THEN UPDATE SET qty = v.qty
WHEN NOT MATCHED THEN INSERT (sku, name, qty) VALUES (v.sku, v.name, v.qty)
OUTPUT $action, inserted.sku, inserted.qty;

-- Oracle（ON 句は括弧で囲む。USING に DUAL を使う）
MERGE INTO stock s
USING (SELECT 'A-002' AS sku, 'ノート' AS name, 20 AS qty FROM dual) v
ON (s.sku = v.sku)
WHEN MATCHED THEN UPDATE SET s.qty = v.qty
WHEN NOT MATCHED THEN INSERT (sku, name, qty) VALUES (v.sku, v.name, v.qty);

SQLiteのUPSERT

SQLite は 3.24.0 で PostgreSQL と同じ形の ON CONFLICT … DO UPDATE に対応し、3.35.0 で RETURNING も使えるようになりました。Python 3.11 標準の sqlite3（SQLite 3.45.1）で実行しました。

import sqlite3
c = sqlite3.connect(":memory:")
print("SQLite", sqlite3.sqlite_version)
c.execute("CREATE TABLE stock (id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL, qty INTEGER NOT NULL)")
c.execute("INSERT INTO stock (sku, name, qty) VALUES ('A-001','ボールペン',10)")
rows = c.execute("""INSERT INTO stock (sku, name, qty) VALUES ('A-001','ボールペン黒',30),('A-002','ノート',5)
ON CONFLICT (sku) DO UPDATE SET name = excluded.name, qty = excluded.qty
RETURNING id, sku, name, qty""").fetchall()
print(rows)

SQLite 3.45.1
[(1, 'A-001', 'ボールペン黒', 30), (2, 'A-002', 'ノート', 5)]

同時実行（2セッション）での動き

UPSERT を1文で書く大きな理由が、同時実行への強さ です。2つのセッションが同じ新しい品番 C-001 を、ほぼ同時に登録する状況を再現しました。セッションAはトランザクション内で UPSERT した後3秒待ってからCOMMITし、その1秒後にセッションBが同じ UPSERT を実行します。

-- セッションA
BEGIN;
INSERT INTO stock (sku, name, qty) VALUES ('C-001','クリップ大',1)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty RETURNING sku, qty;
SELECT pg_sleep(3);
COMMIT;

-- セッションB（Aの1秒後）
INSERT INTO stock (sku, name, qty) VALUES ('C-001','クリップ大',1)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty RETURNING sku, qty;

PostgreSQL の MERGE で、セッションBに出たエラーです。

psql:/tmp/t87_m_b.sql:4: ERROR:  duplicate key value violates unique constraint "stock_sku_key"
DETAIL:  Key (sku)=(M-001) already exists.

（MERGE の検証では品番を M-001 にしています。）

ON CONFLICT と ON DUPLICATE KEY UPDATE は、相手のトランザクションの結果を待ってから「更新」に切り替えるため、一意制約違反になりません。 MERGE は開始時点のスナップショットで「一致しない」と判断して INSERT に進むため、同時に同じキーが入ると一意制約違反になります。PostgreSQL のドキュメントでも、同時実行時の挿入の衝突を避けたい場合は INSERT … ON CONFLICT を検討するよう案内されています。MERGE を同時に走らせる可能性がある場合は、一意制約違反を捕まえて再実行する 処理をアプリ側に用意しておくと安心です。

よくあるエラーと直し方

PostgreSQL のエラーの実行例です。

INSERT INTO stock (sku, name, qty) VALUES ('A-009','定規',1)
ON CONFLICT (name) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

INSERT INTO stock (sku, name, qty) VALUES ('A-004','付箋',1), ('A-004','付箋',2)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

同じ「1文に同じキーが2行」を MySQL で実行すると、エラーにはならず 1行目で挿入、2行目で更新 され、affected は 3（1＋2）、最終的な qty は 2 でした。DB によって扱いが違うので、一括UPSERTの前に入力データの重複を除いておく と、どちらでも同じ結果になります。

UPSERTの使い分け判断表

書く前のチェックリスト

よくある質問

Q. UPSERT は「SELECT して、あれば UPDATE・なければ INSERT」と何が違いますか？ A. SELECT と INSERT の間に別のセッションが同じキーを入れると、INSERT が一意制約違反になります。ON CONFLICT / ON DUPLICATE KEY UPDATE は1文の中で待ち合わせて更新に切り替えるため、この競合が起きません（本記事の2セッション検証で確認）。

Q. MySQL の VALUES() はもう使えませんか？ A. MySQL 8.0 では動きますが、8.0.20 以降は非推奨で警告 1287 が出ます。8.0.19 以降なら VALUES (…) AS new と new.col に書き換える のがおすすめです。

Q. PostgreSQL で UPSERT した行が挿入か更新かを知るには？ A. PG16 までの ON CONFLICT では、RETURNING (xmax = 0) AS inserted という書き方がよく使われます（内部列に依存する方法です）。PG17 以降の MERGE なら、RETURNING merge_action() で INSERT / UPDATE / DELETE を正式に返せます。

Q. MERGE と ON CONFLICT はどちらを使えばよいですか？ A. PostgreSQL で1件ずつの登録・更新なら 同時実行に強い ON CONFLICT、別テーブルとの一括同期で削除も含めたいなら MERGE が向いています。MERGE は同時実行で一意制約違反になりうるので、リトライ処理と組み合わせてください。

まとめ

• UPSERT は一意制約を基準に「あれば更新・なければ挿入」する書き方。DBごとに構文が違う。
• PostgreSQL は ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col、WHERE で差分だけ更新、RETURNING で結果を返せる。
• MySQL は VALUES (…) AS new ON DUPLICATE KEY UPDATE col = new.col。影響行数は 挿入1・更新2・変化なし0。
• REPLACE は削除→挿入。id が変わり、指定しない列は NULL（既定値）になった。
• UPSERT や INSERT IGNORE でも 連番は消費され、欠番が出る（PG・MySQLとも確認）。
• 2セッション同時実行では ON CONFLICT / ON DUPLICATE KEY UPDATE は待って更新、MERGE は一意制約違反 になった。
• MERGE の RETURNING・merge_action()・WHEN NOT MATCHED BY SOURCE は PostgreSQL 17 から。

株式会社RJCでは、UPSERTやトランザクションの動きを仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。