
SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意
当サイトは Amazon.co.jp を宣伝しリンクすることで紹介料を得る手段を提供する、Amazonアソシエイト・プログラムの参加者です。価格・在庫はリンク先の最新情報をご確認ください。
商品マスタを CSV から取り込む、外部 API から届いた会員情報を手元のテーブルに反映する、日ごとの集計値を書き足していく。こうした処理では「同じキーの行がすでにあれば更新し、なければ挿入する」という操作が何度も出てきます。この操作は UPDATE と INSERT を合わせた言葉で UPSERT と呼ばれます。
UPSERT は、SQL の書き方が製品ごとに大きく違う操作の1つです。PostgreSQL と SQLite は INSERT ... ON CONFLICT、MySQL は INSERT ... ON DUPLICATE KEY UPDATE、SQL Server は MERGE を使い、PostgreSQL は 15 から MERGE も使えます。さらに、「アプリで SELECT して、あれば UPDATE・なければ INSERT」という書き方は、同時に2つのリクエストが来ると壊れます。製品ごとの書き方と、同時実行のときに何が保証されるのかを分けて押さえておくと、どの製品でも迷わずに済みます。
この記事では、小さなサンプルテーブルを最初に1回だけ定義し、すべての文をそのデータに対して実行して結果を並べます。
NOTE
記事中の結果は、PostgreSQL 18.6(手元の一時クラスタ)、MySQL 8.4.13(Docker の公式イメージ。既定の設定)、SQLite 3.54.0 で実行したものです。結果表の NULL は NULL 値を表します。製品で結果が違うものは、その都度どの製品の結果かを書いています。2026年11月19日に PostgreSQL 19.0 がリリースされ、ON CONFLICT DO SELECT が加わりましたが、19 は手元で実行しておらず、19 についての記述はリリースノートとドキュメントに基づいています。SQL Server も手元で実行しておらず、Microsoft のドキュメントに基づく記述です。
先に結論 - 早見表
製品ごとの書き方は次のとおりです。
| 製品 | あれば更新・なければ挿入 | あれば何もしない | 競合の判定に使うもの |
|---|---|---|---|
| PostgreSQL | INSERT ... ON CONFLICT (列) DO UPDATE SET ... | ON CONFLICT DO NOTHING | 指定した列のユニークインデックス・制約 |
| PostgreSQL 15 以降 | MERGE INTO ... USING ... ON ... | WHEN MATCHED THEN DO NOTHING | ON に書いた結合条件 |
| MySQL | INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE ... | INSERT IGNORE(重複以外のエラーも警告になる) | 主キーとすべてのユニークキー |
| SQLite 3.24.0 以降 | INSERT ... ON CONFLICT (列) DO UPDATE SET ... | ON CONFLICT DO NOTHING / INSERT OR IGNORE | 指定した列の主キー・ユニーク制約 |
| SQL Server | MERGE INTO ... WITH (HOLDLOCK) ...; | WHEN NOT MATCHED THEN INSERT だけを書く | ON に書いた結合条件 |
あわせて、よく出てくる論点を先にまとめます。
| やりたいこと・気になること | 結論 |
|---|---|
| SELECT してから INSERT か UPDATE を選ぶ | 同時実行で一意性違反になる。ユニーク制約を張り、1文の UPSERT にする |
| 挿入しようとした値を参照する | PostgreSQL・SQLite は EXCLUDED.列、MySQL は行エイリアス new.列(VALUES(列) は非推奨) |
| 値が同じなら更新しない | PostgreSQL・SQLite は DO UPDATE ... WHERE。MySQL は同じ値なら affected rows が 0 になる |
| 挿入か更新かを知りたい | PostgreSQL 18 以降は RETURNING old.列、MERGE は merge_action()(17 以降)、MySQL は affected rows |
| DO NOTHING で既存の行も返したい | RETURNING は既存の行を返さない。CTE で取り直すか、PostgreSQL 19 の DO SELECT |
| 1文の中に同じキーが2回ある | PostgreSQL はエラー。MySQL と SQLite は順に2回処理される |
| ユニーク制約の列に NULL がある | NULL どうしは重複扱いにならず、毎回挿入される。PostgreSQL 15 以降は NULLS NOT DISTINCT |
| MySQL でユニークキーが複数ある | 1行だけが更新され、意図しない行が書き換わることがある |
REPLACE / INSERT OR REPLACE を使う | DELETE してから INSERT するので、指定しなかった列は既定値に戻り、ID も変わる |
| 同時実行で必ず挿入か更新のどちらかにしたい | PostgreSQL は ON CONFLICT DO UPDATE。PostgreSQL の MERGE は一意性違反になり得る |
サンプルデータ
以降の例は、サンプル文具店(仮)の商品テーブル products を使います。sku(商品コード)が主キーで、version は更新のたびに1増やす列です。値が書き換わったかどうかを見やすくするために置いています。
CREATE TABLE products (
sku TEXT PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER NOT NULL,
version INTEGER NOT NULL DEFAULT 1
);
INSERT INTO products (sku, name, price) VALUES
('A001', 'ノート', 300),
('A002', 'ペン', 120),
('A003', '消しゴム', 80);MySQL では TEXT 型の列をそのまま主キーにできないので、sku VARCHAR(10)、name VARCHAR(20)、price INT、version INT で作っています。
| sku | name | price | version |
|---|---|---|---|
| A001 | ノート | 300 | 1 |
| A002 | ペン | 120 | 1 |
| A003 | 消しゴム | 80 | 1 |
ここに、取り込みたい次の3行が届いたとします。
| sku | name | price | 既存の行との関係 |
|---|---|---|---|
| A001 | ノート | 320 | あり。価格が変わった |
| A002 | ペン | 120 | あり。値は同じ |
| A004 | 定規 | 150 | なし。新しい商品 |
期待する結果は「A001 は価格を 320 に更新、A002 はそのまま、A004 は挿入」です。各節の例は、断りがなければ毎回この初期データに戻してから実行しています。
なぜ「SELECT してから INSERT か UPDATE」ではだめなのか
UPSERT の構文を知らなくても、アプリ側で次のように書けば同じことができそうに見えます。
SELECT 1 FROM products WHERE sku = 'A010';
-- 行がなければ
INSERT INTO products (sku, name, price) VALUES ('A010', '付箋', 110);
-- 行があれば
UPDATE products SET price = 110 WHERE sku = 'A010';1つのセッションだけで動かしているうちは問題が起きません。ところが、別のセッションが同じ sku の行をちょうど挿入している最中だと、SELECT の時点ではその行がまだ見えず(コミット前の行は他のセッションから見えません)、両方が「行がない」と判断して INSERT に進みます。
PostgreSQL 18.6 で、セッション1が A010 を挿入してコミットせずに3秒待ち、その1秒後にセッション2が上の書き方を実行した結果です。
found
-------
0
(1 row)
ERROR: duplicate key value violates unique constraint "products_pkey"
DETAIL: Key (sku)=(A010) already exists.SELECT は 0 件を返し、INSERT はセッション1のコミットまで約1.8秒待たされたあとで、一意性違反になりました。ここで大事なのは、エラーになったのは主キー(ユニーク制約)があったからだという点です。ユニーク制約がなければ、同じ sku の行が2つできて、エラーにもなりません。
このため、UPSERT には次の2つが必要です。
- 「同じ行」を判定する列に、主キーかユニーク制約(ユニークインデックス)を張る
- 判定と挿入・更新を1つの文で行う構文を使う
PostgreSQL・MySQL・SQLite の UPSERT 構文は、どれも「ユニーク制約に違反しそうになったら、エラーにする代わりに別の動作をする」という仕組みです。ユニーク制約がない列では UPSERT は成り立ちません。ユニークインデックスそのものについては データベースインデックス入門 を、コミット前の行が見えない理由は トランザクションと ACID・分離レベル入門 を参照してください。
PostgreSQL の INSERT ... ON CONFLICT
PostgreSQL の INSERT には ON CONFLICT 句があり、ユニーク制約や排他制約に違反するときに、エラーにする代わりの動作を指定できます。構文の骨組みは次のとおりです。
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
VALUES ...
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING ... ]
-- conflict_target は次のどちらか
( index_column_name [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
-- conflict_action は次のどちらか
DO NOTHING
DO UPDATE SET column_name = expression [, ...] [ WHERE condition ]何もしないで飛ばす - DO NOTHING
ON CONFLICT DO NOTHING は、競合する行の挿入だけを飛ばし、エラーにしません。
INSERT INTO products (sku, name, price) VALUES
('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150)
ON CONFLICT (sku) DO NOTHING
RETURNING sku, price;| sku | price |
|---|---|
| A004 | 150 |
psql のコマンドタグは INSERT 0 1 で、挿入されたのは A004 の1行だけです。A001 の価格は 300 のまま変わりません。ON CONFLICT を付けずに同じ行を挿入すると、duplicate key value violates unique constraint "products_pkey" のエラーになり、A004 も含めて1行も挿入されません。
DO NOTHING では競合対象((sku) の部分)を省略でき、その場合は使えるすべての制約とユニークインデックスとの競合を飛ばします。
更新する - DO UPDATE と EXCLUDED
ON CONFLICT DO UPDATE は、競合した既存の行を更新します。SET の中では、既存の行をテーブル名(または別名)で、挿入しようとした行を特別なテーブル名 EXCLUDED で参照します。
INSERT INTO products (sku, name, price) VALUES
('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price,
version = products.version + 1;| sku | name | price | version |
|---|---|---|---|
| A001 | ノート | 320 | 2 |
| A002 | ペン | 120 | 2 |
| A003 | 消しゴム | 80 | 1 |
| A004 | 定規 | 150 | 1 |
コマンドタグは INSERT 0 3 で、挿入した1行と更新した2行の合計です。A002 は値が同じなのに version が 2 になっています。PostgreSQL の DO UPDATE は、値が同じでも行を更新します。値が同じなら更新しない方法は、この後の「値が同じなら更新しない」で扱います。
DO UPDATE には競合対象が必須です。省略すると次のエラーになります。
ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
HINT: For example, ON CONFLICT (column_name).SET の左辺にテーブル名を付けることはできません(SET products.price = ... は column "products" of relation "products" does not exist)。一方で右辺の列名にはテーブル名か別名を付けます。SET price = price + 1 と書くと、既存の行の price か EXCLUDED.price か決まらず、column reference "price" is ambiguous になります。長いテーブル名を毎回書くのを避けたいときは、INSERT INTO products AS p のように別名を付けると p.version + 1 と書けます。
PostgreSQL のドキュメントは、ON CONFLICT DO UPDATE について「独立したエラーがない限り、高い同時実行性の下でも、INSERT か UPDATE のどちらかの結果になることを保証する」と書いています。同時実行の挙動は後の節で実際に確かめます。
挿入か更新かを見分ける - RETURNING と PostgreSQL 18 の OLD / NEW
RETURNING を付けると、挿入した行と更新した行の値が返ります。PostgreSQL 18 からは RETURNING で old.列 と new.列 を書けるようになり、更新前と更新後の値を両方受け取れます。ドキュメントによると、ON CONFLICT DO UPDATE を伴う INSERT では old の値が NULL でないことがあり、競合しなかった行の old は NULL です。これを使うと、行ごとに挿入か更新かを判定できます。
INSERT INTO products (sku, name, price) VALUES
('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price, version = products.version + 1
RETURNING sku,
old.price AS old_price,
new.price AS new_price,
new.version,
(old.sku IS NULL) AS inserted;| sku | old_price | new_price | version | inserted |
|---|---|---|---|---|
| A001 | 300 | 320 | 2 | f |
| A002 | 120 | 120 | 2 | f |
| A004 | NULL | 150 | 1 | t |
PostgreSQL 17 以前では old. と new. は使えません。古い記事では xmax = 0 というシステム列を使った判定が紹介されていることがありますが、18 以降はドキュメントに書かれている old を使う方が確実です。
値が同じなら更新しない - DO UPDATE の WHERE
DO UPDATE には WHERE を付けられ、条件が真になった行だけが更新されます。「どれかの列の値が変わったときだけ更新する」には、IS DISTINCT FROM で既存の行と EXCLUDED を比べます。<> ではなく IS DISTINCT FROM を使うのは、NULL を含む列でも正しく比べられるからです(理由は SQL の NULL の扱い方 を参照)。
INSERT INTO products AS p (sku, name, price) VALUES
('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price, version = p.version + 1
WHERE (p.name, p.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price)
RETURNING sku, price, version;| sku | price | version |
|---|---|---|
| A001 | 320 | 2 |
| A004 | 150 | 1 |
コマンドタグは INSERT 0 2 になり、A002 は更新されず version も 1 のままです。RETURNING にも A002 は出てきません。ドキュメントには「WHERE の条件を満たさず更新されなかった行は返らない」と書かれています。
注意点として、ドキュメントは「WHERE が真になった行だけが更新されるが、ON CONFLICT DO UPDATE の動作が選ばれたときは、すべての行がロックされる」とも書いています。更新しなかった A002 も、このトランザクションが終わるまではロックされたままです。
updated_at のような更新日時の列も、この形にしておくと「本当に値が変わったときだけ日時が進む」ようにできます。
INSERT INTO products AS p (sku, name, price) VALUES ('A001', 'ノート', 320)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = now()
WHERE (p.name, p.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price);(サンプルテーブルには updated_at 列がないので、この文は形だけ示しています。)
競合対象の指定 - 列の推論と ON CONSTRAINT
ON CONFLICT (sku) のように列を書くと、PostgreSQL はその列の組み合わせちょうどのユニークインデックスを探して、競合の判定に使います。ドキュメントはこれをユニークインデックスの推論(inference)と呼んでいます。該当するユニークインデックスや制約がない列を書くと、次のエラーになります。
INSERT INTO products (sku, name, price) VALUES ('A002', 'ペン', 130)
ON CONFLICT (name) DO NOTHING;ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification制約の名前で指定する ON CONFLICT ON CONSTRAINT 制約名 もあります。
INSERT INTO products (sku, name, price) VALUES ('A002', 'ペン', 130)
ON CONFLICT ON CONSTRAINT products_pkey DO UPDATE SET price = EXCLUDED.price
RETURNING *;| sku | name | price | version |
|---|---|---|---|
| A002 | ペン | 130 | 1 |
ただしドキュメントは、制約名を直接書くよりも列による推論の方が望ましいことが多い、としています。CREATE UNIQUE INDEX ... CONCURRENTLY で同等のインデックスを作ってから古いものを消すような入れ替えをしても、推論なら引き続き正しく動くからです。
排他制約は DO UPDATE の判定には使えず、判定に使えるのは NOT DEFERRABLE の制約とユニークインデックスだけです。
部分インデックスと ON CONFLICT
論理削除のあるテーブルでは、「削除されていない行の中でだけメールアドレスを一意にする」ために、WHERE 付きのユニークインデックス(部分インデックス)を使うことがあります。
CREATE TABLE accounts (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL,
deleted_at DATE
);
CREATE UNIQUE INDEX accounts_email_active ON accounts (email) WHERE deleted_at IS NULL;
INSERT INTO accounts (email, deleted_at) VALUES
('a@example.com', DATE '2026-01-31'), -- 削除済み
('a@example.com', NULL); -- 有効このテーブルに ON CONFLICT (email) と書くと、部分インデックスが推論されずにエラーになります。
INSERT INTO accounts (email) VALUES ('a@example.com')
ON CONFLICT (email) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
INSERT INTO accounts (email) VALUES ('a@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;
-- 成功(有効な行と競合したので何もしない)競合対象の WHERE は、部分インデックスを推論させるための条件(index_predicate)です。DO UPDATE の後ろに書く WHERE(更新する行の条件)とは別物なので、位置に注意してください。
DO NOTHING では既存の行が返らない
「あれば既存の行の ID を返し、なければ挿入して新しい ID を返す」という処理はよくありますが、DO NOTHING の RETURNING は挿入した行しか返しません。
INSERT INTO products (sku, name, price) VALUES ('A001', 'ノート', 320)
ON CONFLICT (sku) DO NOTHING
RETURNING sku, price;
-- (0 rows)回避策は3つあります。
1つ目は、データを変更する CTE(SQL の WITH 句(CTE)入門 で扱っています)で INSERT の結果を受け取り、空なら既存の行を読み直す方法です。
WITH ins AS (
INSERT INTO products (sku, name, price) VALUES ('A001', 'ノート', 320)
ON CONFLICT (sku) DO NOTHING
RETURNING sku, price, 'inserted' AS src
)
SELECT * FROM ins
UNION ALL
SELECT sku, price, 'existing' FROM products
WHERE sku = 'A001' AND NOT EXISTS (SELECT 1 FROM ins);| sku | price | src |
|---|---|---|
| A001 | 300 | existing |
PostgreSQL のドキュメントによると、WITH の中のデータ変更文と外側の問い合わせはすべて同じスナップショットで実行されます。そのため、別のセッションがコミットしたばかりでこの文のスナップショットからは見えない行と競合した場合、外側の SELECT がその行を読めず0行になることがあります。0行のときはもう一度実行する、という作りにしておくのが安全です。
2つ目は、DO UPDATE で値を変えない更新をかける方法です。
INSERT INTO products (sku, name, price) VALUES ('A001', 'ノート', 320)
ON CONFLICT (sku) DO UPDATE SET sku = EXCLUDED.sku
RETURNING sku, price, version;| sku | price | version |
|---|---|---|
| A001 | 300 | 1 |
確実に行が返りますが、値が同じでも UPDATE として扱われるので、行ロックを取り、UPDATE のトリガーがあれば動きます。
3つ目は、PostgreSQL 19 で加わった ON CONFLICT DO SELECT です。19.0 のリリースノートに「INSERT ... ON CONFLICT DO SELECT ... RETURNING のサポートを追加」とあり、ドキュメントは「INSERT か SELECT のどちらかの結果を原子的に得られる」と説明しています。DO SELECT には RETURNING が必須で、FOR UPDATE などのロックも指定できます。
INSERT INTO products (sku, name, price) VALUES ('A001', 'ノート', 320)
ON CONFLICT (sku) DO SELECT
RETURNING sku, price;19 の新機能は PostgreSQL 19ベータ1の新機能と既定値の変更 でも紹介しています。
PostgreSQL の MERGE
PostgreSQL 15 で、標準 SQL の MERGE が加わりました。15.0 のリリースノートは「ある表を別の表に合わせる MERGE コマンドを追加。INSERT ... ON CONFLICT に似ているが、よりバッチ向け」と説明しています。MERGE は、元データ(USING)と対象テーブルを ON の条件で結合し、一致した行と一致しなかった行のそれぞれに動作を指定します。
MERGE INTO products AS p
USING (VALUES ('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150))
AS s (sku, name, price)
ON p.sku = s.sku
WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (s.name, s.price) THEN
UPDATE SET name = s.name, price = s.price, version = p.version + 1
WHEN NOT MATCHED THEN
INSERT (sku, name, price) VALUES (s.sku, s.name, s.price)
RETURNING merge_action(), p.sku, p.price, p.version;| merge_action | sku | price | version |
|---|---|---|---|
| UPDATE | A001 | 320 | 2 |
| INSERT | A004 | 150 | 1 |
WHEN 句は書いた順に評価され、元データの1行ごとに最初に真になった1つだけが実行されます。A002 は最初の WHEN MATCHED AND ... が偽で、ほかに当てはまる WHEN MATCHED もないので、何もしません。
ON CONFLICT と違って、MERGE はユニーク制約がなくても書けます。どの行が「同じ行」かは ON の結合条件で決まります。その代わり、同時実行のときの保証は ON CONFLICT より弱くなります(後の節で確かめます)。
RETURNING と merge_action() - PostgreSQL 17 以降
上の例の RETURNING と merge_action() は PostgreSQL 17 で加わりました。17.0 のリリースノートには「MERGE で RETURNING 句を使えるようにした。新しい関数 merge_action() は、その行を生成した操作を報告する」とあります。merge_action() は INSERT・UPDATE・DELETE のいずれかを返します。
WHEN NOT MATCHED BY SOURCE で同期する - PostgreSQL 17 以降
同じく 17 で WHEN NOT MATCHED BY SOURCE が加わり、「元データにない行」に対する動作も書けるようになりました。元データを正として、対象テーブルを丸ごと同期させるときに使えます。
MERGE INTO products AS p
USING (VALUES ('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150))
AS s (sku, name, price)
ON p.sku = s.sku
WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (s.name, s.price) THEN
UPDATE SET name = s.name, price = s.price, version = p.version + 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (sku, name, price) VALUES (s.sku, s.name, s.price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
RETURNING merge_action(),
old.sku AS old_sku, old.price AS old_price,
new.sku AS new_sku, new.price AS new_price;| merge_action | old_sku | old_price | new_sku | new_price |
|---|---|---|---|---|
| UPDATE | A001 | 300 | A001 | 320 |
| DELETE | A003 | 80 | NULL | NULL |
| INSERT | NULL | NULL | A004 | 150 |
元データにない A003 が削除されました。実行後のテーブルは A001(320)・A002(120)・A004(150)の3行です。old. と new. は PostgreSQL 18 の機能なので、17 では p.sku のような書き方になります。
ドキュメントによると、標準 SQL が定めているのは WHEN MATCHED と WHEN NOT MATCHED だけで、BY SOURCE・BY TARGET・DO NOTHING・RETURNING は PostgreSQL の拡張です。17 では、自動更新可能なビューを MERGE の対象にできるようにもなりました。
元データに同じキーが2行あるとエラー
MERGE の元データに同じキーの行が2つあると、対象の1行を2回変更しようとしてエラーになります。
MERGE INTO products AS p
USING (VALUES ('A001', 'ノート', 330), ('A001', 'ノート', 340)) AS s (sku, name, price)
ON p.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);ERROR: MERGE command cannot affect row a second time
HINT: Ensure that not more than one source row matches any one target row.まだない行(たとえば A009)が元データに2回あると、2回とも NOT MATCHED になって2回 INSERT し、こちらは duplicate key value violates unique constraint "products_pkey" になります。ドキュメントにも「繰り返された操作が INSERT なら一意性違反、UPDATE か DELETE なら cardinality violation になる」とあります。元データの重複は、MERGE の前に取り除いておく必要があります。
MySQL の INSERT ... ON DUPLICATE KEY UPDATE
MySQL では、INSERT に ON DUPLICATE KEY UPDATE を付けます。主キーかユニークキーの値が重複する行を挿入しようとすると、代わりに既存の行に UPDATE をかけます。PostgreSQL と違って競合対象の列は書かず、主キーとすべてのユニークキーが判定に使われます。
挿入しようとした値の参照 - 行エイリアス AS new
挿入しようとした値は、VALUES (...) の後ろに付けた行エイリアスで参照します。
INSERT INTO products (sku, name, price) VALUES ('A001', 'ノート', 320) AS new
ON DUPLICATE KEY UPDATE
name = new.name,
price = new.price,
version = version + 1;古い書き方の VALUES(price) も MySQL 8.4.13 で動きますが、警告が出ます。
INSERT INTO products (sku, name, price) VALUES ('A004', '定規', 160)
ON DUPLICATE KEY UPDATE price = VALUES(price);
SHOW WARNINGS;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 insteadMySQL 8.4 のマニュアルも、VALUES() で新しい行と列を参照する使い方は非推奨で、将来のバージョンで削除される可能性があるので、行エイリアスと列エイリアスを使うように、と書いています。列エイリアスを付ける書き方もあります。
INSERT INTO products (sku, name, price) VALUES ('A002', 'ペン', 125) AS new(s, n, p)
ON DUPLICATE KEY UPDATE price = p;マニュアルによると、列エイリアスを使うときも VALUES の後ろに行エイリアスが必要で、行エイリアスはテーブル名と同じにできません。
NOTE
行エイリアスがどのバージョンで入り、VALUES() がどのバージョンから非推奨になったかは、MySQL 8.4 のマニュアルの該当ページには書かれておらず、この記事では一次情報で確認できていません(未確認)。8.4 で行エイリアスが使えることと、VALUES() で警告 1287 が出ることは実行して確かめています。
列名があいまいになるとき
行エイリアスを使うと、エイリアス側にも sku・name・price という列ができます。そのため、SET の右辺で既存の行の列を修飾なしで参照すると、あいまいだというエラーになることがあります。MySQL 8.4.13 では、次の文がエラーになりました。
INSERT INTO products (sku, name, price) VALUES ('A003', '消しゴム', 90) AS new
ON DUPLICATE KEY UPDATE
version = IF(price <> new.price, version + 1, version),
price = new.price;
-- ERROR 1052 (23000): Column 'price' in field list is ambiguous一方、最初の例の version = version + 1 はエラーになりませんでした。INSERT の列リストに version を書いていないので、エイリアス側に version がないためと考えられます。どちらの場合も、既存の行の列は products.price のようにテーブル名で修飾しておくと確実です。
affected rows が 1・2・0 になる
MySQL では、ON DUPLICATE KEY UPDATE の affected rows(影響を受けた行数)が、行ごとに次のように数えられます。マニュアルの記述と、実行結果が一致しました。
| 取り込んだ行 | 起きたこと | affected rows(ROW_COUNT()) |
|---|---|---|
| A004(定規, 150) | 新しい行として挿入 | 1 |
| A001(ノート, 320) | 既存の行を別の値に更新 | 2 |
| A002(ペン, 120) | 既存の行を同じ値に更新(実際には変わらない) | 0 |
マニュアルには、接続時に CLIENT_FOUND_ROWS フラグを指定すると、同じ値で更新した行も 0 ではなく 1 になる、とも書かれています。アプリが使うドライバによってこのフラグの既定値が違うことがあるので、affected rows で「挿入か更新か」を判定する場合は、手元の環境で確かめてください。
複数の行をまとめて送ったときは合計値が返るので、行ごとの内訳は分かりません。MySQL には RETURNING がなく(MySQL 8.4.13 で INSERT ... RETURNING は構文エラー)、行ごとに挿入か更新かを知りたい場合は、1行ずつ実行するか、別の方法で読み直す必要があります。
代入は左から順に評価される
「値が変わったときだけ version を増やす」を MySQL で書くと次のようになります。
INSERT INTO products (sku, name, price) VALUES ('A003', '消しゴム', 90) AS new
ON DUPLICATE KEY UPDATE
version = IF(products.price <> new.price, products.version + 1, products.version),
price = new.price;A003 は価格が 80 から 90 に変わり、version が 2 になりました。同じ文で価格を 80 のまま送ると、affected rows は 0 で version も 1 のままです。
ここで SET の順番を入れ替えて、price = new.price を先に書くとどうなるでしょうか。A003(price 90, version 2)に価格 100 を送った結果です。
INSERT INTO products (sku, name, price) VALUES ('A003', '消しゴム', 100) AS new
ON DUPLICATE KEY UPDATE
price = new.price,
version = IF(products.price <> new.price, products.version + 1, products.version);| sku | name | price | version |
|---|---|---|---|
| A003 | 消しゴム | 100 | 2 |
価格は変わったのに version が増えていません。MySQL の UPDATE の代入は左から順に評価され、後の式は前の代入で書き換わった値を見るためです。price が先に 100 になったので、IF の比較は 100 と 100 になって偽になりました。PostgreSQL の SET はすべて更新前の値を見るので、この違いは製品をまたいで SQL を移植するときに注意が必要です。
複数のユニークキーがあるテーブルでは別の行が更新される
MySQL の ON DUPLICATE KEY UPDATE は、主キーとすべてのユニークキーで重複を判定します。挿入しようとした行が、ある行と主キーで、別の行とユニークキーで重複すると、更新されるのはどちらか1行だけです。
次の items テーブルは、id(AUTO_INCREMENT の主キー)と sku(ユニークキー)の2つのキーを持ちます。
CREATE TABLE items (
id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
price INT NOT NULL,
memo VARCHAR(20) NOT NULL DEFAULT ''
);この節の時点で、items には次の3行があります(id が飛んでいるのは、後の AUTO_INCREMENT と REPLACE の節の操作をした後の状態だからです)。
| id | sku | name | price |
|---|---|---|---|
| 2 | A005 | のり | 210 |
| 5 | A004 | 定規 | 150 |
| 8 | A001 | ノート | 340 |
ここに「id 8、sku A004、価格 160」を送ります。id 8 は A001 の行と、sku A004 は id 5 の行と重複します。
INSERT INTO items (id, sku, name, price) VALUES (8, 'A004', '定規', 160) AS new
ON DUPLICATE KEY UPDATE price = new.price;
-- Query OK, 2 rows affected| id | sku | name | price |
|---|---|---|---|
| 2 | A005 | のり | 210 |
| 5 | A004 | 定規 | 150 |
| 8 | A001 | ノート | 160 |
A004 の価格を変えたかったのに、主キーで重複した A001(id 8)の価格が 160 に書き換わりました。エラーにも警告にもなりません。マニュアルも、このような場合は1行しか更新されないと説明したうえで、「一般に、複数のユニークインデックスを持つテーブルでは ON DUPLICATE KEY UPDATE を使わないようにするべき」と書いています。また、主キーかユニークキーが2つ以上あるテーブルへの ON DUPLICATE KEY UPDATE は、ステートメントベースのレプリケーションでは安全でない文として扱われます。
実務では、AUTO_INCREMENT の id を INSERT の値に含めず、業務上のキー(ここでは sku)だけで重複させるようにすると、この事故は起きにくくなります。
AUTO_INCREMENT の欠番
AUTO_INCREMENT の列があるテーブルでは、ON DUPLICATE KEY UPDATE が更新になったときも、自動採番の値が消費されることがあります。前の節と同じ形の items テーブルを作り直し、A001(id 1、memo は「定番」)と A002(id 2)だけを入れた状態で、A001 への UPSERT を2回、続けて新しい A004 の UPSERT を実行した結果です。
INSERT INTO items (sku, name, price) VALUES ('A001', 'ノート', 320) AS new
ON DUPLICATE KEY UPDATE price = new.price; -- 更新
INSERT INTO items (sku, name, price) VALUES ('A001', 'ノート', 330) AS new
ON DUPLICATE KEY UPDATE price = new.price; -- 更新
INSERT INTO items (sku, name, price) VALUES ('A004', '定規', 150) AS new
ON DUPLICATE KEY UPDATE price = new.price; -- 挿入| id | sku | name | price |
|---|---|---|---|
| 1 | A001 | ノート | 330 |
| 2 | A002 | ペン | 120 |
| 5 | A004 | 定規 | 150 |
A004 の id は 3 ではなく 5 になりました。2回の更新で 3 と 4 が使われたためです。マニュアルにも、InnoDB のテーブルでは、この INSERT は(同じ結果になる UPDATE と違って)auto-increment の値を増やす、と書かれています。手元の環境(公式イメージの既定の設定)では、innodb_autoinc_lock_mode は 2 でした。
更新が多い取り込みを繰り返すと、id の欠番が増えていきます。id に連番であることを期待しない設計にするのが基本です。INT の上限が気になるほど回数が多いなら、先に SELECT で存在を確かめて更新だけの行は UPDATE で済ませる、BIGINT にする、といった対策を検討します。
updated_at と ON UPDATE CURRENT_TIMESTAMP
MySQL では、列に ON UPDATE CURRENT_TIMESTAMP を付けると、行が更新されたときに日時が自動で入ります。ON DUPLICATE KEY UPDATE で同じ値を送った場合(affected rows が 0)は日時が変わらず、別の値を送った場合(affected rows が 2)は日時が更新されることを確かめました。値が同じときに更新しないための条件を自分で書く必要はありません。
MySQL の REPLACE と INSERT IGNORE
MySQL には UPSERT に似た文がほかに2つあります。どちらも手軽ですが、ON DUPLICATE KEY UPDATE とは動きが大きく違います。
REPLACE は DELETE してから INSERT する
REPLACE は、重複する行があればそれを削除してから、新しい行を挿入します。AUTO_INCREMENT の節の続きで、items が A001(id 1、memo は「定番」)・A002(id 2)・A004(id 5)・A005(id 7)の状態から、A001 に REPLACE を実行した結果です。
REPLACE INTO items (sku, name, price) VALUES ('A001', 'ノート', 340);
-- Query OK, 2 rows affected| id | sku | name | price | memo |
|---|---|---|---|---|
| 2 | A002 | ペン | 120 | |
| 5 | A004 | 定規 | 150 | |
| 7 | A005 | のり | 200 | |
| 8 | A001 | ノート | 340 |
affected rows は削除1行と挿入1行で 2 です。結果を見ると次のことが分かります。
- A001 の
idが 1 から 8 に変わった。外部キーでこのidを参照している行があると、参照先が消える(制約の設定によっては、エラーや連鎖削除になる) - 文で指定しなかった
memoが、「定番」から既定値の空文字に戻った - 削除と挿入なので、DELETE と INSERT のトリガーが動く
ユニークキーが複数あると、1回の REPLACE で複数の行が消えます。id 2 が A002、id 7 が A005 の状態で、「id 2、sku A005」を REPLACE した結果です。
REPLACE INTO items (id, sku, name, price) VALUES (2, 'A005', 'のり', 210);
-- Query OK, 3 rows affected| id | sku | name | price |
|---|---|---|---|
| 2 | A005 | のり | 210 |
| 5 | A004 | 定規 | 150 |
| 8 | A001 | ノート | 340 |
A002(id 2)と A005(id 7)の2行が削除され、1行が挿入されて affected rows は 3 になりました。A002 の行はどこにも残っていません。「あれば更新」のつもりで REPLACE を使うと、このように行が消えることがあります。値を部分的に更新したいなら ON DUPLICATE KEY UPDATE を使います。
INSERT IGNORE は重複以外のエラーも警告にする
INSERT IGNORE は、重複する行の挿入を警告にして飛ばします。
INSERT IGNORE INTO items (sku, name, price) VALUES ('A002', 'ペン', 999);
-- Query OK, 0 rows affected, 1 warning
-- Warning | 1062 | Duplicate entry 'A002' for key 'items.sku'ここまでは PostgreSQL の DO NOTHING と同じに見えますが、IGNORE が警告に変えるのは重複だけではありません。STRICT_TRANS_TABLES を含む既定の SQL モード(公式イメージの既定の設定)で、次の2つを実行した結果です。
INSERT IGNORE INTO items (sku, name, price)
VALUES ('A006', 'とても長い名前の商品をここに入れてみると切り詰められる', 100);
-- Query OK, 1 row affected, 1 warning
-- Warning | 1265 | Data truncated for column 'name' at row 1
INSERT IGNORE INTO items (sku, name, price) VALUES ('A007', '付箋', NULL);
-- Query OK, 1 row affected, 1 warning
-- Warning | 1048 | Column 'price' cannot be null| id | sku | name | price |
|---|---|---|---|
| 9 | A006 | とても長い名前の商品をここに入れてみると | 100 |
| 10 | A007 | 付箋 | 0 |
VARCHAR(20) に収まらない名前は切り詰められ、NOT NULL の price に NULL を入れようとした行は 0 で挿入されました。IGNORE を付けなければ、後者は ERROR 1048 (23000): Column 'price' cannot be null で失敗します。アプリが警告を見ていなければ、壊れたデータが黙って入ります。また、INSERT IGNORE で飛ばした行でも AUTO_INCREMENT の値は消費されました。REPLACE の例の前の状態で A004 が id 5、A005 が id 7 になっていて 6 が欠番なのは、A004 の挿入後に INSERT IGNORE で A002 を飛ばし、その後に A005 を挿入したためです。
重複だけを飛ばしたいなら、ON DUPLICATE KEY UPDATE sku = sku のように値を変えない更新を書く方が、ほかのエラーを握りつぶさずに済みます。
SQLite の UPSERT
SQLite は、PostgreSQL とほぼ同じ ON CONFLICT 句を持っています。SQLite のドキュメントによると、UPSERT の構文は 3.24.0(2018-06-04)で加わりました。当初は PostgreSQL にならって ON CONFLICT 句を1つだけ書けて、DO UPDATE には競合対象が必須でしたが、3.35.0(2021-03-12)で ON CONFLICT 句を複数書けるようになり、DO UPDATE の競合対象も省略できるようになりました。
INSERT INTO products AS p (sku, name, price) VALUES
('A001', 'ノート', 320), ('A002', 'ペン', 120), ('A004', '定規', 150)
ON CONFLICT (sku) DO UPDATE
SET name = excluded.name, price = excluded.price, version = p.version + 1
WHERE (p.name, p.price) IS NOT (excluded.name, excluded.price)
RETURNING sku, price, version;| sku | price | version |
|---|---|---|
| A001 | 320 | 2 |
| A004 | 150 | 1 |
changes() は 2 で、PostgreSQL と同じく A002 は更新されません。SQLite では IS NOT が NULL を考慮した「等しくない」になります。DO UPDATE の中で修飾なしの列名を書くと、挿入前の既存の値を指し、挿入しようとした値は excluded. を付けて参照します。
PostgreSQL と違う点もあります。
- 競合対象を省略した
ON CONFLICT DO UPDATEも書ける(3.35.0 以降)。SQLite 3.54.0 では、主キーと競合した A002 が更新された DO UPDATEの更新で制約違反が起きると、常にABORTとして扱われ、INSERT 文全体が取り消される(ドキュメントより)- 仮想テーブルでは UPSERT が使えない(ドキュメントより)
MERGEはない(SQLite 3.54.0 でMERGEは構文エラー)
INSERT ... SELECT のときは WHERE true を付ける
INSERT ... SELECT に ON CONFLICT を付けると、構文の解釈があいまいになることがあります。ON が UPSERT の始まりなのか、結合の ON なのかを区別できないためです。
INSERT INTO products (sku, name, price) SELECT sku, name, price FROM price_import
ON CONFLICT (sku) DO UPDATE SET price = excluded.price;
-- Parse error: near "DO": syntax error
INSERT INTO products (sku, name, price) SELECT sku, name, price FROM price_import WHERE true
ON CONFLICT (sku) DO UPDATE SET price = excluded.price;
-- 成功ドキュメントも、SELECT には(WHERE true だけでもよいので)常に WHERE 句を付けるように、と書いています。
INSERT OR REPLACE・INSERT OR IGNORE との違い
SQLite には INSERT OR REPLACE と INSERT OR IGNORE もあります。MySQL の items と同じ形のテーブル(id INTEGER PRIMARY KEY、sku がユニーク、memo の既定値は空文字)に、A001(id 1、memo は「定番」)と A002(id 2)を入れて INSERT OR REPLACE を実行した結果です。
INSERT OR REPLACE INTO items (sku, name, price) VALUES ('A001', 'ノート', 340);
SELECT changes(); -- 1| id | sku | name | price | memo |
|---|---|---|---|---|
| 2 | A002 | ペン | 120 | |
| 3 | A001 | ノート | 340 |
MySQL の REPLACE と同じく、既存の行が消えて新しい行が入ったので、id が 1 から 3 に変わり、memo が既定値に戻りました。changes() は 1 で、削除した行は数えられていません。INSERT OR IGNORE は重複する行を飛ばし、changes() は 0 でした。
既存の行の一部の列だけを更新したいなら、INSERT OR REPLACE ではなく ON CONFLICT DO UPDATE を使います。
SQL Server と標準 SQL の MERGE
SQL Server では、UPSERT に MERGE を使います(ここは手元で実行しておらず、Microsoft Learn のドキュメントに基づく記述です)。
MERGE INTO dbo.products WITH (HOLDLOCK) AS p
USING (VALUES ('A001', N'ノート', 320), ('A004', N'定規', 150)) AS s (sku, name, price)
ON p.sku = s.sku
WHEN MATCHED AND (p.name <> s.name OR p.price <> s.price) THEN
UPDATE SET name = s.name, price = s.price, version = p.version + 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (sku, name, price) VALUES (s.sku, s.name, s.price)
OUTPUT $action, inserted.sku, inserted.price;ドキュメントから、UPSERT に関係する注意点を拾うと次のとおりです。
MERGE文は、文の終わりにセミコロンが必須。付けないとエラー 10713 になる- 元データの複数の行が対象の1行に一致すると、エラーになる。
MERGEは同じ行を2回更新したり、更新と削除を両方行ったりできない - 挿入と更新の両方が起き得る場面では、
HOLDLOCKを指定すると一意キー違反を防げる。HOLDLOCKはSERIALIZABLE分離レベルと同じ意味で、最も安全だが同時実行性は最も低い - 同時実行が多いときは、
MERGEよりも別々のINSERT・UPDATE・DELETEの方が、ブロックが少なく性能がよいことがある。本番に入れる前に十分にテストすること OUTPUT $actionで、行ごとにINSERT・UPDATE・DELETEのどれが行われたかを返せる
WHEN NOT MATCHED BY SOURCE は SQL Server の MERGE にもある書き方です。上の例の p.name <> s.name は、NULL を含む列では正しく比べられないので、NULL があり得るなら条件を書き足してください。
書き方ごとの違いの比較表
ここまでの結果をまとめます。
| 項目 | PostgreSQL ON CONFLICT | PostgreSQL MERGE | MySQL ON DUPLICATE KEY UPDATE | MySQL REPLACE | SQLite ON CONFLICT |
|---|---|---|---|---|---|
| ユニーク制約が必要か | 必要 | 不要(ON の条件で判定) | 必要 | 必要 | 必要 |
| 判定に使うキー | 指定した列・制約 | ON の条件 | すべての主キー・ユニークキー | すべての主キー・ユニークキー | 指定した列(省略も可) |
| 新しい値の参照 | EXCLUDED.列 | USING 側の列 | new.列(行エイリアス) | 不要(行ごと入れ替え) | excluded.列 |
| 値が同じとき | 更新する(WHERE で止める) | WHEN MATCHED AND で止める | 実際には変えない(affected rows 0) | 削除して挿入 | 更新する(WHERE で止める) |
| 更新か挿入かの確認 | RETURNING old.列(18 以降) | merge_action()(17 以降) | affected rows の合計 | affected rows の合計 | RETURNING で返る行 |
| 1文に同じキーが2回 | エラー | エラー(既存行)/一意性違反(新規行) | 順に2回更新 | 順に処理 | 順に2回更新 |
| 元データにない行の削除 | できない | WHEN NOT MATCHED BY SOURCE(17 以降) | できない | できない | できない |
| 同時に同じキーを挿入 | 待ってから更新 | 一意性違反になり得る | 待ってから更新 | 未検証 | 未検証 |
同時実行時の挙動
UPSERT の構文を使う大きな理由は、同時実行への強さです。PostgreSQL 18.6 で、冒頭と同じ条件(セッション1が A010 を挿入してコミットせずに3秒待ち、その1秒後にセッション2が実行)を4通り試しました。分離レベルはどちらも既定の Read Committed です。セッション2の文は、4通りともセッション1のコミットまで約1.8秒待たされてから結果を返しました。
| セッション2が実行した文 | セッション2の結果 | 最終的な A010 の行 |
|---|---|---|
| SELECT してから INSERT | SELECT は 0 件、INSERT は待ってから一意性違反 | price 100(セッション1の値) |
INSERT ... ON CONFLICT DO UPDATE | 待ってから更新に成功 | price 110、version 2 |
MERGE(MATCHED で UPDATE、NOT MATCHED で INSERT) | 待ってから一意性違反 | price 100(セッション1の値) |
INSERT ... ON CONFLICT DO NOTHING RETURNING * | 待ってから 0 行 | price 100(セッション1の値) |
ON CONFLICT DO UPDATE は、セッション1のコミットを待ったあと、挿入ではなく更新に切り替わりました。PostgreSQL のドキュメント(トランザクションの分離の章)は、Read Committed では挿入しようとした各行が挿入か更新のどちらかになることが保証され、競合の相手がまだこの INSERT から見えない別のトランザクションの行であっても、UPDATE はその行に対して行われる、と説明しています。
一方、MERGE はセッション1の行を「一致しない」と判断して INSERT に進み、一意性違反で失敗しました。ドキュメントにも次のように書かれています。
INSERT と UPDATE の両方を指定した MERGE は、ON CONFLICT DO UPDATE を付けた INSERT に似て見えるが、INSERT か UPDATE のどちらかが起きることを保証しない。MERGE が INSERT を試みたときにユニークインデックスがあり、重複する行が同時に挿入されると、一意性違反のエラーになる。MERGE は、MATCHED の条件の評価をやり直してそのようなエラーを避けようとはしない。
(PostgreSQL 18 のドキュメントの該当箇所を訳したものです。)
MERGE のドキュメント自体も、同時に INSERT が起きた場合に UPDATE を実行できる代わりの文として INSERT ... ON CONFLICT を挙げています。PostgreSQL で、同時に同じキーが来る可能性がある UPSERT を書くなら ON CONFLICT を使い、MERGE を使う場合は一意性違反のときに再実行する処理を入れるか、同時実行が起きない場面(夜間のバッチなど)に限るのが安全です。
MySQL 8.4.13(既定の REPEATABLE READ)でも同じ条件で ON DUPLICATE KEY UPDATE を試したところ、セッション2はセッション1のコミットを待ったあと、affected rows 2 で更新に成功しました(price 110、version 2)。SQLite での同時実行は、この記事では試していません。
分離レベルごとの見え方の違いは トランザクションと ACID・分離レベル入門 で詳しく扱っています。
一括 UPSERT
UPSERT を1行ずつループで発行すると、行数分の往復が発生します。N+1問題 と同じで、まとめて1文で送る方が速くなります。
VALUES に複数の行を並べるほか、PostgreSQL では配列を unnest で展開すると、行数が変わってもプレースホルダーの数を固定できます。
INSERT INTO products AS p (sku, name, price)
SELECT * FROM unnest(ARRAY['A002', 'A005'], ARRAY['ペン', 'のり'], ARRAY[125, 200])
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price
RETURNING sku, price;| sku | price |
|---|---|
| A002 | 125 |
| A005 | 200 |
CSV などを一時テーブルに読み込んでから取り込むときは、同じキーの行が元データに複数含まれていないかに注意します。PostgreSQL の ON CONFLICT DO UPDATE は、1つの文で同じ行を2回更新しようとするとエラーになるためです。
INSERT INTO products (sku, name, price) VALUES
('A001', 'ノート', 330), ('A001', 'ノート', 340)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;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.ドキュメントはこれを、ON CONFLICT DO UPDATE を伴う INSERT は「決定的な」文であり、既存の1行に2回以上影響することは許されず、cardinality violation のエラーになる、と説明しています。同じ文を DO NOTHING にすると、エラーにはならず、2行目は1行目と競合したものとして飛ばされました。
取り込み元の重複は、どの行を採用するかを決めてから取り除きます。PostgreSQL なら DISTINCT ON が手軽です。
-- price_import: ('A001','ノート',310,1), ('A001','ノート',320,2), ('A004','定規',150,1)
INSERT INTO products AS p (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM price_import
ORDER BY sku, imported_at DESC -- sku ごとに一番新しい行を採用
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price, version = p.version + 1
WHERE p.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price, version;| sku | price | version |
|---|---|---|
| A001 | 320 | 2 |
| A004 | 150 | 1 |
DISTINCT ON を使わない場合は、ウィンドウ関数 の ROW_NUMBER() で1番目の行だけを残す方法もあります。
MySQL と SQLite は、同じキーが2回あってもエラーにならず、順に2回処理されました。MySQL では A001 が 330 に更新されたあと 340 に更新され、version は 2 回増えて affected rows は 4 でした。SQLite でも RETURNING が A001 を2行(330 と 340)返しています。エラーにならない分、後勝ちで上書きされていることに気付きにくいので、取り込み元の重複はどの製品でも先に取り除いておく方が安全です。
つまずきとよくあるエラー
NULL はユニーク制約で重複扱いにならない
ユニーク制約の列に NULL が入っていると、UPSERT が競合を検出できず、毎回挿入されます。倉庫ごとの在庫を (sku, warehouse) で一意にし、倉庫が未定の行を warehouse NULL で表すテーブルに、同じ行を2回 UPSERT した結果です。
CREATE TABLE stocks (
sku TEXT NOT NULL,
warehouse TEXT,
qty INTEGER NOT NULL,
UNIQUE (sku, warehouse)
);
INSERT INTO stocks VALUES ('A001', NULL, 10)
ON CONFLICT (sku, warehouse) DO UPDATE SET qty = stocks.qty + EXCLUDED.qty;
INSERT INTO stocks VALUES ('A001', NULL, 5)
ON CONFLICT (sku, warehouse) DO UPDATE SET qty = stocks.qty + EXCLUDED.qty;| sku | warehouse | qty |
|---|---|---|
| A001 | NULL | 10 |
| A001 | NULL | 5 |
PostgreSQL 18.6 と SQLite 3.54.0 の両方で、合計 15 の1行ではなく2行になりました。NULL どうしは「等しい」と判定されないので、ユニーク制約に違反せず、ON CONFLICT も発動しません。
PostgreSQL 15 以降では、UNIQUE NULLS NOT DISTINCT で NULL を同じ値として扱えます。15.0 のリリースノートに「ユニーク制約とインデックスで NULL を区別しない扱いにできるようにした」とあり、既定は従来どおり NULL を区別する(複数入れられる)動きです。
CREATE TABLE stocks (
sku TEXT NOT NULL,
warehouse TEXT,
qty INTEGER NOT NULL,
UNIQUE NULLS NOT DISTINCT (sku, warehouse)
);
-- 同じ2回の UPSERT のあと| sku | warehouse | qty |
|---|---|---|
| A001 | NULL | 15 |
MySQL や SQLite、PostgreSQL 14 以前では、NULL の代わりに「未定」を表す値(空文字や '-' など)を入れて NOT NULL にするのが確実です。NULL とユニーク制約の関係は SQL の NULL の扱い方 でも扱っています。
よくあるエラーメッセージ
ここまでに出てきたエラーと、その原因をまとめます。
| エラー | 製品 | 原因と対処 |
|---|---|---|
duplicate key value violates unique constraint | PostgreSQL | 競合対象と違う制約で重複した、MERGE で同時に挿入された、など。競合対象の指定と同時実行を見直す |
ERROR 1062 (23000): Duplicate entry ... for key | MySQL | ON DUPLICATE KEY UPDATE を付けていない、または更新した値が別のキーと重複した |
there is no unique or exclusion constraint matching the ON CONFLICT specification | PostgreSQL | 競合対象の列にユニーク制約がない。部分インデックスなら競合対象に WHERE を書く |
ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint | SQLite | 同上 |
ON CONFLICT DO UPDATE requires inference specification or constraint name | PostgreSQL | DO UPDATE に競合対象を書いていない |
ON CONFLICT DO UPDATE command cannot affect row a second time | PostgreSQL | 1文の中に同じキーが2回ある。取り込み元の重複を除く |
MERGE command cannot affect row a second time | PostgreSQL | MERGE の元データに同じキーが2回ある |
column reference "price" is ambiguous | PostgreSQL | SET の右辺で既存の行の列を修飾していない。products.price か別名で書く |
ERROR 1052 (23000): Column 'price' in field list is ambiguous | MySQL | 行エイリアスと同じ名前の列を修飾していない。products.price で書く |
Warning 1287: 'VALUES function' is deprecated | MySQL | VALUES(列) を行エイリアス new.列 に書き換える |
Parse error: near "DO": syntax error | SQLite | INSERT ... SELECT に WHERE がない。WHERE true を付ける |
ほかに、PostgreSQL のドキュメントは、ユニークインデックスに対して CREATE INDEX CONCURRENTLY や REINDEX CONCURRENTLY を実行している間は、同じテーブルへの INSERT ... ON CONFLICT が予期せず一意性違反で失敗することがある、と注意しています。また、パーティションテーブルでは、ON CONFLICT DO UPDATE で競合した行のパーティションキーを、別のパーティションへ移る値に更新することは現在サポートされていません。
まとめ
- UPSERT は「あれば更新、なければ挿入」。SELECT してから INSERT か UPDATE を選ぶ書き方は、同時実行で一意性違反になる(ユニーク制約がなければ重複した行ができる)。判定に使う列にユニーク制約を張り、1文の UPSERT にする。
- PostgreSQL は
INSERT ... ON CONFLICT (列) DO UPDATE SET 列 = EXCLUDED.列。DO UPDATEには競合対象が必須で、値が同じでも更新するので、WHERE ... IS DISTINCT FROM EXCLUDEDで止める。 - PostgreSQL 18 以降は
RETURNING old.列, new.列で更新前後の値と、挿入か更新かが分かる。DO NOTHINGのRETURNINGは既存の行を返さないので、CTE で読み直すか、PostgreSQL 19 のDO SELECTを使う。 - 部分ユニークインデックスを使うときは、
ON CONFLICT (列) WHERE 条件と競合対象に条件を書く。 - PostgreSQL の
MERGEは 15 以降。17 でRETURNING・merge_action()・WHEN NOT MATCHED BY SOURCEが加わった。ユニーク制約がなくても書けるが、同時に同じキーが挿入されると一意性違反になり得る。 - MySQL は
INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE 列 = new.列。VALUES(列)は非推奨。affected rows は挿入 1・更新 2・同じ値 0。SETの代入は左から順に評価される。 - MySQL ではすべての主キーとユニークキーが判定に使われ、ユニークキーが複数あると意図しない行が更新される。更新でも AUTO_INCREMENT の値が消費される。
- MySQL の
REPLACEと SQLite のINSERT OR REPLACEは削除してから挿入するので、ID が変わり、指定しなかった列が既定値に戻り、複数の行が消えることもある。MySQL のINSERT IGNOREは重複以外のエラーも警告にしてしまう。 - SQLite の UPSERT は 3.24.0 以降で、PostgreSQL とほぼ同じ書き方。3.35.0 から
ON CONFLICT句を複数書け、DO UPDATEの競合対象も省略できる。INSERT ... SELECTにはWHERE trueを付ける。 - SQL Server は
MERGEを使い、文末のセミコロンが必須。同時実行で一意キー違反を防ぐにはHOLDLOCKを指定する。 - 1文の中に同じキーが2回あると、PostgreSQL はエラー、MySQL と SQLite は後勝ちで2回更新する。取り込み元の重複は先に除く。
- NULL はユニーク制約で重複扱いにならず、UPSERT が毎回挿入になる。PostgreSQL 15 以降は
NULLS NOT DISTINCT、それ以外は NULL を使わない設計にする。
UPSERT が思ったとおりに動かないときは、まず「どのユニーク制約で競合を判定しているか」を確かめてください。判定に使う制約が想定と違う、NULL が入っていて競合しない、取り込み元に同じキーが2回ある、のどれかであることがほとんどです。
参考リンク
- INSERT(PostgreSQL 公式)
- MERGE(PostgreSQL 公式)
- Transaction Isolation(PostgreSQL 公式)
- CREATE INDEX(PostgreSQL 公式・NULLS NOT DISTINCT)
- PostgreSQL 15.0 リリースノート
- PostgreSQL 17.0 リリースノート
- PostgreSQL 18.0 リリースノート
- PostgreSQL 19.0 リリースノート
- INSERT ... ON DUPLICATE KEY UPDATE Statement(MySQL 8.4 リファレンスマニュアル)
- REPLACE Statement(MySQL 8.4 リファレンスマニュアル)
- INSERT Statement(MySQL 8.4 リファレンスマニュアル)
- AUTO_INCREMENT Handling in InnoDB(MySQL 8.4 リファレンスマニュアル)
- UPSERT(SQLite 公式)
- The ON CONFLICT Clause(SQLite 公式)
- INSERT(SQLite 公式)
- RETURNING clause(SQLite 公式)
- MERGE (Transact-SQL)(Microsoft Learn)
関連記事
- SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意
- SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法
- SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け
- SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴
- SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序
- SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
- SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け
- データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
- トランザクションとACID・分離レベル入門 - dirty read / phantom と PostgreSQL・MySQLの違い
- N+1問題とは - ORM で起きる原因と Eager Loading・JOIN・バッチ取得での対策
- PostgreSQL 19ベータ1の新機能と既定値の変更


