MERGE / UPSERT(存在すれば更新、なければ挿入)

Oracle

MERGE INTO customer m
USING (SELECT 1 AS id, 'Ali' AS name, 500 AS balance FROM dual) k
ON (m.id = k.id)
WHEN MATCHED THEN
  UPDATE SET m.name = k.name, m.balance = k.balance
WHEN NOT MATCHED THEN
  INSERT (id, name, balance) VALUES (k.id, k.name, k.balance);

MERGE(upsert)はソースをターゲットと照合し、一致する行を更新し、一致しない行を挿入する。一括のデータ同期に最適。Oracle は強力な MERGE をサポートし、WHEN MATCHED ... DELETE や条件付きの更新も記述できる。単一行のソースには dual テーブルを使用する。

PostgreSQL

-- The PostgreSQL way: INSERT ... ON CONFLICT
INSERT INTO customer (id, name, balance)
VALUES (1, 'Ali', 500)
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name, balance = EXCLUDED.balance;

PostgreSQL の従来の upsert は INSERT ... ON CONFLICT(一意の列)DO UPDATE / DO NOTHING。競合した行の新しい値には EXCLUDED エイリアスでアクセスする。PostgreSQL 15 以降は標準の MERGE 文もサポートされる。ON CONFLICT は単一のテーブルでアトミックであり、広く使用されている。

MySQL

INSERT INTO customer (id, name, balance)
VALUES (1, 'Ali', 500)
ON DUPLICATE KEY UPDATE
  name = VALUES(name), balance = VALUES(balance);

MySQL/MariaDB は upsert に INSERT ... ON DUPLICATE KEY UPDATE を使用する。一意キーまたは主キーが競合すると、その行が更新される。新しい値には VALUES(my_column)(8.0.19+ では行エイリアス 'new.my_column')でアクセスする。INSERT IGNORE は競合時にその行を黙ってスキップする。MySQL には標準の MERGE はない。

SQL Server

MERGE INTO customer AS m
USING (VALUES (1, 'Ali', 500)) AS k(id, name, balance)
ON m.id = k.id
WHEN MATCHED THEN
  UPDATE SET m.name = k.name, m.balance = k.balance
WHEN NOT MATCHED THEN
  INSERT (id, name, balance) VALUES (k.id, k.name, k.balance);

SQL Server は 2008 以降 MERGE 文をサポートし、ターゲットの余分な行を削除するための WHEN NOT MATCHED BY SOURCE などの追加の分岐を提供する。MERGE 文はセミコロンで終える必要がある。注: 一部のエッジケースのバグのため、単純な upsert では INSERT/UPDATE 文を個別に書くほうが好ましい場合がある。

MongoDB

// Find-and-update or insert with upsert:true
db.customer.updateOne(
  { id: 1 },
  { $set: { name: "Ali", balance: 500 } },
  { upsert: true }
)

MongoDB では upsert はネイティブのオプション。updateOne/updateMany/findOneAndUpdate に { upsert: true } を追加すると、一致するドキュメントは更新され、そうでなければ新しいドキュメントが作成される。一括の同期には、集計の $merge ステージ(ターゲットコレクションへの書き込み/更新)が SQL の MERGE により近い。

Redis

# SET is a natural upsert: it overwrites the key if present, otherwise creates it.
SET customer:1 '{"name":"Ali","balance":500}'
# Update/add a hash field:
HSET customer:1 name Ali balance 500

Redis の書き込みコマンドは本質的に upsert: SET/HSET はキーまたはフィールドが存在すれば更新し、なければ作成する。存在しない場合のみ書き込むには SET ... NX オプションを、存在する場合のみなら XX を使用する。「存在すれば更新、なければ挿入」の区別は、ほとんどのコマンドで既にデフォルトの挙動。

Elasticsearch

# An index call with a document id acts like an upsert (replaces if present):
PUT customer/_doc/1
{ "name": "Ali", "balance": 500 }
# Partial update + insert if absent:
POST customer/_update/1
{ "doc": { "balance": 500 }, "doc_as_upsert": true }

Elasticsearch では、同じ _id での index(PUT)呼び出しは、ドキュメントが存在すれば完全に置き換え、なければ作成する(upsert に類似)。存在しない場合は挿入する部分更新には、_update API で 'doc_as_upsert': true または 'upsert' ブロックを使用する。スクリプトによる upsert も可能。

DBAtlas で開く →