Description: One of the most exciting new features addressing the needs for ETL is the SQL statement MERGE. The new SQL combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality.
Comment: (none)
Language: Highlight Mode: PLSQL Last Modified: March 07th, 2009
Syntax -
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 THENUPDATESET b.bonus = e.salary *0.1DELETEWHERE(e.salary <40000)WHENNOT MATCHED THENINSERT(b.employee_id, b.bonus)VALUES(e.employee_id, e.salary *0.05)WHERE(e.salary >40000);
