Oracle merge

oracle merge

語法：

用途：

       Use the MERGE statement to select rows from one or more sources for update or
insertion into a table or view. You can specify conditions to determine whether to
update or insert into the target table or view.

        It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements

用於從一個或多個原表查詢數據插入或者更新目標表。避免多次執行insert，delete，update操作

例子：

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);

這裡稍微提醒下，insert後面沒有into