Note: Primarily of value when moving large amounts of data in data warehouse situations.
Merge Statement Demo
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer |
unlimited>];
MERGE INTO bonuses b
USING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 DELETEWHERE (e.salary < 40000) WHENNOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05) WHERE (e.salary > 40000);