【Oracle】MERGE文（UPSERT）完全ガイド｜WHEN MATCHED/NOT MATCHED・条件付きMERGE・DELETE句・ORA-30926・実務パターンまで実例で解説

MERGE INTO ターゲット表 t          -- 更新・挿入先
USING ソース表またはサブクエリ s   -- 比較元データ
ON (結合条件)                      -- マッチング条件（主キー等）

WHEN MATCHED THEN
    -- 条件が一致した行: UPDATE または DELETE
    UPDATE SET
        t.列1 = s.列1,
        t.列2 = s.列2

WHEN NOT MATCHED THEN
    -- 条件が一致しなかった行: INSERT
    INSERT (列1, 列2, 列3)
    VALUES (s.列1, s.列2, s.列3);

-- 入荷データ（ソース）を使って在庫テーブル（ターゲット）を更新する
-- 既存商品: 在庫数を加算 / 新商品: 新規 INSERT

MERGE INTO inventory t              -- ターゲット: 在庫テーブル
USING (
    -- ソース: 入荷データ（サブクエリも使える）
    SELECT product_id, quantity, unit_price
    FROM incoming_shipment
    WHERE shipment_date = TRUNC(SYSDATE)
) s
ON (t.product_id = s.product_id)   -- 商品IDでマッチング

WHEN MATCHED THEN
    -- 既存商品: 在庫を加算・単価を更新
    UPDATE SET
        t.stock_qty  = t.stock_qty + s.quantity,
        t.unit_price = s.unit_price,
        t.updated_at = SYSDATE

WHEN NOT MATCHED THEN
    -- 新商品: 新規登録
    INSERT (product_id, stock_qty, unit_price, created_at)
    VALUES (s.product_id, s.quantity, s.unit_price, SYSDATE);

-- 実行後: SQL%ROWCOUNT で処理件数を確認
-- COMMIT を忘れずに
COMMIT;

-- ターゲットに存在する行のみ更新（INSERT はしない）
MERGE INTO employees t
USING (
    SELECT employee_id, new_salary
    FROM salary_revision
) s
ON (t.employee_id = s.employee_id)

WHEN MATCHED THEN
    UPDATE SET t.salary = s.new_salary
    -- WHEN NOT MATCHED は省略: ソースにあってターゲットにない行は無視
;
COMMIT;

-- 差分挿入: ターゲットに存在しないデータだけを INSERT する
-- (INSERT IGNORE / INSERT OR IGNORE の代替)
MERGE INTO products t
USING (
    SELECT product_id, product_name, category
    FROM new_products_staging
) s
ON (t.product_id = s.product_id)

WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, created_at)
    VALUES (s.product_id, s.product_name, s.category, SYSDATE)
    -- WHEN MATCHED は省略: 既存行は変更しない
;
COMMIT;

-- 実際に値が変わった場合のみ UPDATE する（不要な UPDATE を削減）
MERGE INTO products t
USING products_staging s
ON (t.product_id = s.product_id)

WHEN MATCHED AND (
    t.product_name != s.product_name OR
    t.unit_price   != s.unit_price   OR
    t.category     != s.category
) THEN
    UPDATE SET
        t.product_name = s.product_name,
        t.unit_price   = s.unit_price,
        t.category     = s.category,
        t.updated_at   = SYSDATE

WHEN NOT MATCHED AND s.is_active = 1 THEN
    -- アクティブな商品のみ INSERT する
    INSERT (product_id, product_name, unit_price, category, created_at)
    VALUES (s.product_id, s.product_name, s.unit_price, s.category, SYSDATE);
COMMIT;

-- ソースの is_active = 0 の行は UPDATE した後に DELETE する
MERGE INTO products t
USING products_staging s
ON (t.product_id = s.product_id)

WHEN MATCHED THEN
    UPDATE SET
        t.product_name = s.product_name,
        t.unit_price   = s.unit_price,
        t.is_active    = s.is_active
    DELETE WHERE t.is_active = 0   -- UPDATE 後に is_active=0 の行を削除

WHEN NOT MATCHED AND s.is_active = 1 THEN
    INSERT (product_id, product_name, unit_price, is_active)
    VALUES (s.product_id, s.product_name, s.unit_price, 1);
COMMIT;

-- 注意: DELETE は WHEN MATCHED THEN UPDATE とセットでしか使えない
-- DELETE 単独の WHEN MATCHED THEN DELETE という構文は存在しない

-- NG: ソースに product_id=100 が2行あると ORA-30926 が発生する
MERGE INTO products t
USING products_staging s   -- product_id=100 が重複して存在
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
    UPDATE SET t.unit_price = s.unit_price;
-- → ORA-30926: ソース表の安定した行セットを取得できません

-- OK: ソースを集約して重複を排除する
MERGE INTO products t
USING (
    -- サブクエリで重複を排除（最新の unit_price を優先）
    SELECT product_id,
           MAX(unit_price) AS unit_price   -- または MIN、または ROW_NUMBER() で1件に絞る
    FROM products_staging
    GROUP BY product_id
) s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
    UPDATE SET t.unit_price = s.unit_price;

-- ROW_NUMBER を使って最新1件に絞る方法
MERGE INTO products t
USING (
    SELECT product_id, unit_price
    FROM (
        SELECT product_id, unit_price,
               ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) AS rn
        FROM products_staging
    )
    WHERE rn = 1
) s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
    UPDATE SET t.unit_price = s.unit_price;

CREATE OR REPLACE PROCEDURE sync_products AS
    v_start_time TIMESTAMP := SYSTIMESTAMP;
    v_rows_merged NUMBER := 0;
BEGIN
    -- ステージングテーブルのデータを本番テーブルに同期
    MERGE INTO products t
    USING (
        -- ソースを集約して重複を排除（ORA-30926 対策）
        SELECT product_id,
               MAX(product_name) KEEP (DENSE_RANK LAST ORDER BY load_seq) AS product_name,
               MAX(unit_price)   KEEP (DENSE_RANK LAST ORDER BY load_seq) AS unit_price,
               MAX(category)     KEEP (DENSE_RANK LAST ORDER BY load_seq) AS category,
               MAX(is_active)    KEEP (DENSE_RANK LAST ORDER BY load_seq) AS is_active
        FROM products_staging
        WHERE load_date = TRUNC(SYSDATE)
        GROUP BY product_id
    ) s
    ON (t.product_id = s.product_id)

    WHEN MATCHED AND (
        t.product_name != s.product_name OR
        t.unit_price   != s.unit_price   OR
        t.category     != s.category     OR
        t.is_active    != s.is_active
    ) THEN
        UPDATE SET
            t.product_name = s.product_name,
            t.unit_price   = s.unit_price,
            t.category     = s.category,
            t.is_active    = s.is_active,
            t.updated_at   = SYSDATE
        DELETE WHERE t.is_active = 0

    WHEN NOT MATCHED AND s.is_active = 1 THEN
        INSERT (product_id, product_name, unit_price, category, is_active, created_at)
        VALUES (s.product_id, s.product_name, s.unit_price,
                s.category, 1, SYSDATE);

    v_rows_merged := SQL%ROWCOUNT;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE(
        '同期完了: ' || v_rows_merged || '件, 処理時間: ' ||
        EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)) || '秒'
    );

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('エラー: ' || SQLERRM);
        RAISE;
END sync_products;
/