SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

作成日:
読了:約73分
更新日:

当サイトは 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 のドキュメントに基づく記述です。

先に結論 - 早見表

製品ごとの書き方は次のとおりです。

製品あれば更新・なければ挿入あれば何もしない競合の判定に使うもの
PostgreSQLINSERT ... ON CONFLICT (列) DO UPDATE SET ...ON CONFLICT DO NOTHING指定した列のユニークインデックス・制約
PostgreSQL 15 以降MERGE INTO ... USING ... ON ...WHEN MATCHED THEN DO NOTHINGON に書いた結合条件
MySQLINSERT ... 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 ServerMERGE 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増やす列です。値が書き換わったかどうかを見やすくするために置いています。

サンプルテーブルの定義とデータ(PostgreSQL・SQLite)
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 で作っています。

skunamepriceversion
A001ノート3001
A002ペン1201
A003消しゴム801

ここに、取り込みたい次の3行が届いたとします。

skunameprice既存の行との関係
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が上の書き方を実行した結果です。

セッション2の結果(PostgreSQL 18.6)
 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. 「同じ行」を判定する列に、主キーかユニーク制約(ユニークインデックス)を張る
  2. 判定と挿入・更新を1つの文で行う構文を使う

PostgreSQL・MySQL・SQLite の UPSERT 構文は、どれも「ユニーク制約に違反しそうになったら、エラーにする代わりに別の動作をする」という仕組みです。ユニーク制約がない列では UPSERT は成り立ちません。ユニークインデックスそのものについては データベースインデックス入門 を、コミット前の行が見えない理由は トランザクションと ACID・分離レベル入門 を参照してください。

PostgreSQL の INSERT ... ON CONFLICT

PostgreSQL の INSERT には ON CONFLICT 句があり、ユニーク制約や排他制約に違反するときに、エラーにする代わりの動作を指定できます。構文の骨組みは次のとおりです。

ON CONFLICT 句の形(PostgreSQL 18 のドキュメントより抜粋)
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;
skuprice
A004150

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;
skunamepriceversion
A001ノート3202
A002ペン1202
A003消しゴム801
A004定規1501

コマンドタグは 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 です。これを使うと、行ごとに挿入か更新かを判定できます。

更新前後の値と、挿入か更新かを返す(PostgreSQL 18 以降)
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;
skuold_pricenew_priceversioninserted
A0013003202f
A0021201202f
A004NULL1501t

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;
skupriceversion
A0013202
A0041501

コマンドタグは INSERT 0 2 になり、A002 は更新されず version も 1 のままです。RETURNING にも A002 は出てきません。ドキュメントには「WHERE の条件を満たさず更新されなかった行は返らない」と書かれています。

注意点として、ドキュメントは「WHERE が真になった行だけが更新されるが、ON CONFLICT DO UPDATE の動作が選ばれたときは、すべての行がロックされる」とも書いています。更新しなかった A002 も、このトランザクションが終わるまではロックされたままです。

updated_at のような更新日時の列も、この形にしておくと「本当に値が変わったときだけ日時が進む」ようにできます。

値が変わったときだけ 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 *;
skunamepriceversion
A002ペン1301

ただしドキュメントは、制約名を直接書くよりも列による推論の方が望ましいことが多い、としています。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 は挿入した行しか返しません。

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);
skupricesrc
A001300existing

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;
skupriceversion
A0013001

確実に行が返りますが、値が同じでも UPDATE として扱われるので、行ロックを取り、UPDATE のトリガーがあれば動きます。

3つ目は、PostgreSQL 19 で加わった ON CONFLICT DO SELECT です。19.0 のリリースノートに「INSERT ... ON CONFLICT DO SELECT ... RETURNING のサポートを追加」とあり、ドキュメントは「INSERT か SELECT のどちらかの結果を原子的に得られる」と説明しています。DO SELECT には RETURNING が必須で、FOR UPDATE などのロックも指定できます。

PostgreSQL 19 の DO SELECT(手元では未実行)
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 で値が変わった行だけ更新し、ない行を挿入する
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_actionskupriceversion
UPDATEA0013202
INSERTA0041501

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 が加わり、「元データにない行」に対する動作も書けるようになりました。元データを正として、対象テーブルを丸ごと同期させるときに使えます。

元データにない行を削除して同期する(PostgreSQL 17 以降)
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_actionold_skuold_pricenew_skunew_price
UPDATEA001300A001320
DELETEA00380NULLNULL
INSERTNULLNULLA004150

元データにない 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回変更しようとしてエラーになります。

元データに同じキーが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 (...) の後ろに付けた行エイリアスで参照します。

行エイリアスで新しい値を参照する(MySQL)
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 で動きますが、警告が出ます。

VALUES() 関数を使う古い書き方
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 instead

MySQL 8.4 のマニュアルも、VALUES() で新しい行と列を参照する使い方は非推奨で、将来のバージョンで削除される可能性があるので、行エイリアスと列エイリアスを使うように、と書いています。列エイリアスを付ける書き方もあります。

列エイリアスを付ける書き方(MySQL)
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 では、次の文がエラーになりました。

修飾なしの price があいまいになる(MySQL)
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 で書くと次のようになります。

値が変わったときだけ 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 を送った結果です。

代入の順番を入れ替える(MySQL)
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);
skunamepriceversion
A003消しゴム1002

価格は変わったのに version が増えていません。MySQL の UPDATE の代入は左から順に評価され、後の式は前の代入で書き換わった値を見るためです。price が先に 100 になったので、IF の比較は 100 と 100 になって偽になりました。PostgreSQL の SET はすべて更新前の値を見るので、この違いは製品をまたいで SQL を移植するときに注意が必要です。

複数のユニークキーがあるテーブルでは別の行が更新される

MySQL の ON DUPLICATE KEY UPDATE は、主キーとすべてのユニークキーで重複を判定します。挿入しようとした行が、ある行と主キーで、別の行とユニークキーで重複すると、更新されるのはどちらか1行だけです。

次の items テーブルは、id(AUTO_INCREMENT の主キー)と sku(ユニークキー)の2つのキーを持ちます。

キーが2つあるテーブル(MySQL)
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 の節の操作をした後の状態だからです)。

idskunameprice
2A005のり210
5A004定規150
8A001ノート340

ここに「id 8、sku A004、価格 160」を送ります。id 8 は A001 の行と、sku A004 は id 5 の行と重複します。

主キーとユニークキーで別々の行と重複する(MySQL)
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
idskunameprice
2A005のり210
5A004定規150
8A001ノート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 を実行した結果です。

更新でも AUTO_INCREMENT が進む(MySQL 8.4.13・既定の設定)
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;   -- 挿入
idskunameprice
1A001ノート330
2A002ペン120
5A004定規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 で A001 を置き換える(MySQL)
REPLACE INTO items (sku, name, price) VALUES ('A001', 'ノート', 340);
-- Query OK, 2 rows affected
idskunamepricememo
2A002ペン120
5A004定規150
7A005のり200
8A001ノート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(MySQL)
REPLACE INTO items (id, sku, name, price) VALUES (2, 'A005', 'のり', 210);
-- Query OK, 3 rows affected
idskunameprice
2A005のり210
5A004定規150
8A001ノート340

A002(id 2)と A005(id 7)の2行が削除され、1行が挿入されて affected rows は 3 になりました。A002 の行はどこにも残っていません。「あれば更新」のつもりで REPLACE を使うと、このように行が消えることがあります。値を部分的に更新したいなら ON DUPLICATE KEY UPDATE を使います。

INSERT IGNORE は重複以外のエラーも警告にする

INSERT IGNORE は、重複する行の挿入を警告にして飛ばします。

重複を飛ばす(MySQL)
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つを実行した結果です。

重複以外のエラーも警告になる(MySQL)
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
idskunameprice
9A006とても長い名前の商品をここに入れてみると100
10A007付箋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 の競合対象も省略できるようになりました。

SQLite の UPSERT(値が変わった行だけ更新)
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;
skupriceversion
A0013202
A0041501

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 なのかを区別できないためです。

SELECT から取り込むときは WHERE が必要(SQLite)
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(SQLite)
INSERT OR REPLACE INTO items (sku, name, price) VALUES ('A001', 'ノート', 340);
SELECT changes();  -- 1
idskunamepricememo
2A002ペン120
3A001ノート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 のドキュメントに基づく記述です)。

SQL Server の MERGE(ドキュメントに基づく例・未実行)
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 CONFLICTPostgreSQL MERGEMySQL ON DUPLICATE KEY UPDATEMySQL REPLACESQLite 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 してから INSERTSELECT は 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 で展開すると、行数が変わってもプレースホルダーの数を固定できます。

配列でまとめて UPSERT する(PostgreSQL)
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;
skuprice
A002125
A005200

CSV などを一時テーブルに読み込んでから取り込むときは、同じキーの行が元データに複数含まれていないかに注意します。PostgreSQL の ON CONFLICT DO UPDATE は、1つの文で同じ行を2回更新しようとするとエラーになるためです。

1文に同じキーが2回ある(PostgreSQL)
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 が手軽です。

取り込み元の重複を除いてから UPSERT する(PostgreSQL)
-- 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;
skupriceversion
A0013202
A0041501

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 した結果です。

NULL を含むユニーク制約で 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;
skuwarehouseqty
A001NULL10
A001NULL5

PostgreSQL 18.6 と SQLite 3.54.0 の両方で、合計 15 の1行ではなく2行になりました。NULL どうしは「等しい」と判定されないので、ユニーク制約に違反せず、ON CONFLICT も発動しません。

PostgreSQL 15 以降では、UNIQUE NULLS NOT DISTINCT で NULL を同じ値として扱えます。15.0 のリリースノートに「ユニーク制約とインデックスで NULL を区別しない扱いにできるようにした」とあり、既定は従来どおり NULL を区別する(複数入れられる)動きです。

NULLS NOT DISTINCT にする(PostgreSQL 15 以降)
CREATE TABLE stocks (
  sku       TEXT NOT NULL,
  warehouse TEXT,
  qty       INTEGER NOT NULL,
  UNIQUE NULLS NOT DISTINCT (sku, warehouse)
);
-- 同じ2回の UPSERT のあと
skuwarehouseqty
A001NULL15

MySQL や SQLite、PostgreSQL 14 以前では、NULL の代わりに「未定」を表す値(空文字や '-' など)を入れて NOT NULL にするのが確実です。NULL とユニーク制約の関係は SQL の NULL の扱い方 でも扱っています。

よくあるエラーメッセージ

ここまでに出てきたエラーと、その原因をまとめます。

エラー製品原因と対処
duplicate key value violates unique constraintPostgreSQL競合対象と違う制約で重複した、MERGE で同時に挿入された、など。競合対象の指定と同時実行を見直す
ERROR 1062 (23000): Duplicate entry ... for keyMySQLON DUPLICATE KEY UPDATE を付けていない、または更新した値が別のキーと重複した
there is no unique or exclusion constraint matching the ON CONFLICT specificationPostgreSQL競合対象の列にユニーク制約がない。部分インデックスなら競合対象に WHERE を書く
ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraintSQLite同上
ON CONFLICT DO UPDATE requires inference specification or constraint namePostgreSQLDO UPDATE に競合対象を書いていない
ON CONFLICT DO UPDATE command cannot affect row a second timePostgreSQL1文の中に同じキーが2回ある。取り込み元の重複を除く
MERGE command cannot affect row a second timePostgreSQLMERGE の元データに同じキーが2回ある
column reference "price" is ambiguousPostgreSQLSET の右辺で既存の行の列を修飾していない。products.price か別名で書く
ERROR 1052 (23000): Column 'price' in field list is ambiguousMySQL行エイリアスと同じ名前の列を修飾していない。products.price で書く
Warning 1287: 'VALUES function' is deprecatedMySQLVALUES(列) を行エイリアス new.列 に書き換える
Parse error: near "DO": syntax errorSQLiteINSERT ... 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回ある、のどれかであることがほとんどです。

参考リンク

関連記事

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

約68分

SQL の CASE 式を、同じ小さなサンプルデータに対する実行結果を並べて整理します。単純 CASE と検索 CASE、ELSE を省略すると NULL になること、上から順に評価して最初に一致した分岐を返すこと、WHEN NULL が一致しない理由、SELECT・WHERE・GROUP BY・HAVING・ORDER BY・UPDATE など書ける場所、年齢層・金額帯の区分け、SUM(CASE ...)・COUNT(CASE ...) の条件付き集計とピボット、FILTER 句との比較、任意の並び順、UPDATE で複数の値を1文で書き換える方法と ELSE 漏れの事故、NULLIF による 0 除算の回避、COALESCE・GREATEST・LEAST・MySQL の IF()・SQLite の iif() との関係、結果の型の決まり方とエラー、短絡評価が効かない場面(定数式の早期評価と集約関数)、WHERE の CASE とインデックス、ストアドプロシージャの CASE 文との違いまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

約58分

SQL のサブクエリ(副問い合わせ)を、同じ小さなサンプルデータに対する実行結果を並べて整理します。スカラーサブクエリと 0 行・複数行のときの挙動、IN と JOIN の違い、EXISTS と NOT EXISTS、NOT IN に NULL が混ざると 0 行になる罠と回避方法、相関サブクエリ、FROM 句のサブクエリ(派生テーブル)と別名の要否、ANY・SOME・ALL と空集合、LATERAL と LEFT JOIN LATERAL、JOIN・CTE との使い分け、UPDATE・DELETE での注意、セミジョイン・アンチジョインへの変換と PostgreSQL の EXPLAIN での見え方、MySQL 8.4 の semijoin・antijoin 最適化、よくあるエラーまでを、各公式ドキュメントを一次ソースにまとめます。

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

約54分

SQL の GROUP BY と HAVING を、同じ小さなサンプルデータに対する実行結果を並べて整理します。COUNT(*) と COUNT(列) と COUNT(DISTINCT 列) の違い、SUM・AVG・MIN・MAX と NULL、WHERE と HAVING の違いと FROM・WHERE・GROUP BY・HAVING・SELECT・ORDER BY の論理的な処理順序、SELECT の別名を GROUP BY や HAVING で使えるかの製品差、PostgreSQL の関数従属と MySQL の ONLY_FULL_GROUP_BY、SQLite の bare column、LEFT JOIN で 0 件のグループを出す方法、FILTER 句と CASE 式による条件付き集計、ROLLUP・GROUPING SETS・CUBE と MySQL の WITH ROLLUP、ウィンドウ関数との違い、よくあるエラー、WHERE で先に絞る理由とインデックスまでを、各公式ドキュメントを一次ソースにまとめます。

楽天市場のおすすめPR / 楽天アフィリエイト