【Oracle】結合を用いたデータ更新の方法完全ガイド｜相関サブクエリ・更新可能結合ビュー・MERGE・ORA-01779対処まで解説

-- 社員テーブル（更新対象）
CREATE TABLE employees (
    emp_id        NUMBER PRIMARY KEY,
    emp_name      VARCHAR2(100),
    dept_id       NUMBER,
    salary        NUMBER,
    dept_name     VARCHAR2(100)   -- ← これをマスタから反映したい
);

-- 部門マスタ（参照元）
CREATE TABLE departments (
    dept_id       NUMBER PRIMARY KEY,
    dept_name     VARCHAR2(100),
    location      VARCHAR2(100)
);

-- departments テーブルの dept_name を employees に反映
UPDATE employees e
SET e.dept_name = (
    SELECT d.dept_name
    FROM departments d
    WHERE d.dept_id = e.dept_id
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
);

-- dept_name と location を同時に更新
UPDATE employees e
SET (e.dept_name, e.location) = (
    SELECT d.dept_name, d.location
    FROM departments d
    WHERE d.dept_id = e.dept_id
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
);

-- インラインビューを UPDATE する
UPDATE (
    SELECT
        e.dept_name  AS emp_dept_name,   -- 更新対象の列
        d.dept_name  AS new_dept_name    -- 参照元の列
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
)
SET emp_dept_name = new_dept_name;

UPDATE (
    SELECT
        e.dept_name  AS emp_dept_name,
        e.location   AS emp_location,
        d.dept_name  AS new_dept_name,
        d.location   AS new_location
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
)
SET emp_dept_name = new_dept_name,
    emp_location  = new_location;

ORA-01779: キー保存されない表にマップする列は変更できません
(cannot modify a column which maps to a non key-preserved table)

-- departments.dept_id に PRIMARY KEY も UNIQUE INDEX もない場合
UPDATE (
    SELECT e.salary, d.new_salary
    FROM employees e
    JOIN dept_salary_changes d ON e.dept_id = d.dept_id
    -- dept_salary_changes.dept_id にユニーク制約がない → ORA-01779
)
SET salary = new_salary;

-- 参照テーブルの結合キーにユニークインデックスを作成
CREATE UNIQUE INDEX idx_dept_salary_dept_id
ON dept_salary_changes (dept_id);

-- これで更新可能結合ビューが使えるようになる
UPDATE (
    SELECT e.salary, d.new_salary
    FROM employees e
    JOIN dept_salary_changes d ON e.dept_id = d.dept_id
)
SET salary = new_salary;  -- OK

-- departments の dept_name を employees に反映
MERGE INTO employees e
USING departments d
ON (e.dept_id = d.dept_id)
WHEN MATCHED THEN
    UPDATE SET e.dept_name = d.dept_name;

-- 複数列の更新 + WHERE で条件を追加
MERGE INTO employees e
USING departments d
ON (e.dept_id = d.dept_id)
WHEN MATCHED THEN
    UPDATE SET
        e.dept_name = d.dept_name,
        e.location  = d.location
    WHERE e.salary > 0;  -- 追加条件: salary が 0 より大きい行のみ更新

-- マスタに存在する部門のみ dept_name を更新
UPDATE employees e
SET e.dept_name = (
    SELECT d.dept_name FROM departments d WHERE d.dept_id = e.dept_id
)
WHERE EXISTS (
    SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id
);

-- マスタに存在しない部門ID の dept_name を「（廃止部門）」に更新
UPDATE employees e
SET e.dept_name = '（廃止部門）'
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id
);

-- IN で絞り込む（小規模データ向け）
UPDATE employees
SET dept_name = '本社'
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = 'Tokyo'
);

-- 大量データでは EXISTS の方がパフォーマンスが良いことが多い

DECLARE
    v_emp_id   NUMBER;
    v_new_name VARCHAR2(100);
BEGIN
    UPDATE employees
    SET dept_name = '新事業部'
    WHERE emp_id = 1001
    RETURNING emp_id, dept_name INTO v_emp_id, v_new_name;

    DBMS_OUTPUT.PUT_LINE('Updated: ' || v_emp_id || ' → ' || v_new_name);
    DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
END;
/

DECLARE
    TYPE t_ids IS TABLE OF NUMBER;
    v_ids t_ids;
BEGIN
    UPDATE employees e
    SET e.dept_name = (
        SELECT d.dept_name FROM departments d WHERE d.dept_id = e.dept_id
    )
    WHERE EXISTS (
        SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id
    )
    RETURNING e.emp_id BULK COLLECT INTO v_ids;

    DBMS_OUTPUT.PUT_LINE('Updated ' || v_ids.COUNT || ' rows');
END;
/

-- マスタの dept_name が変更されたとき、orders テーブルの表示用列を更新
MERGE INTO orders o
USING departments d
ON (o.dept_id = d.dept_id)
WHEN MATCHED THEN
    UPDATE SET o.dept_name_display = d.dept_name
    WHERE o.dept_name_display <> d.dept_name  -- 変更がある行のみ更新
       OR o.dept_name_display IS NULL;

-- CSV 取り込み用ステージングから本番テーブルを更新
MERGE INTO products p
USING staging_products s
ON (p.product_id = s.product_id)
WHEN MATCHED THEN
    UPDATE SET
        p.product_name = s.product_name,
        p.price        = s.price,
        p.updated_at   = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price, created_at, updated_at)
    VALUES (s.product_id, s.product_name, s.price, SYSDATE, SYSDATE);

-- 各部門の平均給与を departments テーブルに書き戻す
UPDATE departments d
SET d.avg_salary = (
    SELECT AVG(e.salary)
    FROM employees e
    WHERE e.dept_id = d.dept_id
)
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

-- 社員がいない部門は NULL のまま残る（WHERE EXISTS のおかげ）

-- 部門内の給与順位を rank 列に格納
MERGE INTO employees e
USING (
    SELECT emp_id,
           DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
    FROM employees
) r
ON (e.emp_id = r.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.salary_rank = r.salary_rank;