MERGE / UPSERT (SQL MERGE Statement)

MERGE 語句可以在單一語句中同時執行 INSERT、UPDATE、DELETE 操作，根據條件決定要新增、更新還是刪除資料。這種操作也常被稱為 UPSERT（UPDATE + INSERT 的組合）。

使用情境

當你需要同步兩個資料表，或是「有資料就更新，沒資料就新增」時，MERGE 是很實用的語法。

SQL Server / Oracle - MERGE 語法

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.price = source.price
WHEN NOT MATCHED THEN
    INSERT (id, name, price) VALUES (source.id, source.name, source.price);

範例

假設有兩個資料表：

products（目標表）：

new_products（來源表）：

執行 MERGE：

MERGE INTO products AS p
USING new_products AS np
ON p.id = np.id
WHEN MATCHED THEN
    UPDATE SET p.name = np.name, p.price = np.price
WHEN NOT MATCHED THEN
    INSERT (id, name, price) VALUES (np.id, np.name, np.price);

執行後 products 會變成：

包含 DELETE 操作

MERGE INTO products AS p
USING new_products AS np
ON p.id = np.id
WHEN MATCHED AND np.price = 0 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET p.price = np.price
WHEN NOT MATCHED THEN
    INSERT (id, name, price) VALUES (np.id, np.name, np.price);

MySQL - INSERT ... ON DUPLICATE KEY UPDATE

MySQL 沒有 MERGE 語句，但提供了類似功能：

INSERT INTO products (id, name, price)
VALUES (2, '香蕉', 25)
ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price);

MySQL 8.0+ 使用 VALUES() 的替代寫法

INSERT INTO products (id, name, price)
VALUES (2, '香蕉', 25) AS new
ON DUPLICATE KEY UPDATE name = new.name, price = new.price;

PostgreSQL - INSERT ... ON CONFLICT

PostgreSQL 使用 ON CONFLICT 子句：

INSERT INTO products (id, name, price)
VALUES (2, '香蕉', 25)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price;

如果只想忽略衝突，不做任何更新：

INSERT INTO products (id, name, price)
VALUES (2, '香蕉', 25)
ON CONFLICT (id) DO NOTHING;

SQLite - INSERT OR REPLACE

INSERT OR REPLACE INTO products (id, name, price)
VALUES (2, '香蕉', 25);

或使用 UPSERT 語法（SQLite 3.24+）：

INSERT INTO products (id, name, price)
VALUES (2, '香蕉', 25)
ON CONFLICT (id) DO UPDATE
SET name = excluded.name, price = excluded.price;

各資料庫語法比較