UPSERTing and Logging What Happens
By cj on Jul 12, 2006
In the SQL world, the early name for a combined INSERT and UPDATE statement was "UPSERT". It's a nifty one-stop way to update values when rows are already present and insert new rows when nothing previously existed. The SQL 2003 standard called the command MERGE because it merges two data sources together, either adding missing rows to the target table, or updating existing rows in the target with new values.
Oracle 9i introduced MERGE, and it was enhanced in Oracle 10g to allow not only the combination of INSERT and UPDATE commands, but also DELETE.
The example given in the Oracle SQL documentation is:
MERGE INTO bonuses DSee full MERGE syntax for the full example.
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*0.1)
WHERE (S.salary <= 8000);
Another feature introduced in Oracle 10g for MERGE, INSERT, UPDATE and DELETE is the error logging clause. This "lets you capture DML errors and the log column values of the affected rows and save them in an error logging table." There's no point me wasting bits here: check the error logging example in the INSERT documentation.