How do I Insert or Update all in one statement?

Isn't it a pain when you've got a bunch of records and some of them you need to insert and some you need to update an existing record? You've got to select out the records which aren't in there and then update the ones that already there. A real pain, no doubt.



If your database is Oracle 9i or higher you can use the Merge statement. It looks like this:


MERGE [hint] INTO [schema.] table [t_alias] USING [schema.]
{ table view subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

merge_update_clause
UPDATE SET column = { expr DEFAULT } [, column = { expr DEFAULT }]...

merge_insert_clause
INSERT ( column [, column]... ) VALUES ( expr [, expr]... )

EXAMPLE:


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
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);