The MERGE statement, introduced in Oracle 9i Release 2, is
often called an “upsert” because it can
both update and insert rows in the same pass. It’s a real timesaver for
Extract, Transform, and Load (ETL) applications, such as loading a data
warehouse. Rows that don’t already exist in the data warehouse are inserted,
and rows that do exist are updated.
When the MERGE statement was introduced,
both an UPDATE and an INSERT clause were required, and the order was fixed
(first the UPDATE, then the INSERT). If you only wanted to do one or the
other, you would use the existing INSERT or UPDATE statement instead of MERGE.
Deletions were always done separately via the DELETE statement.
In Oracle 10g Release 1, the MERGE statement syntax changed
in two ways. The UPDATE or INSERT clauses became optional, so you could do
either or both. Also, a DELETE capability was added to the UPDATE clause. You
can now clean up obsolete records during the same run as valid records are
Listing A creates
a table that lists open projects: a project number, title, start date,
percentage completed, and the employee responsible for the project. It also
creates a transaction table that will be used to perform a batch update upon it
A typical MERGE statement to perform the update starts by
identifying the tables involved, and the match
condition to test for existing records:
MERGE INTO open_projects op
ON (op.pno = pu.pno)
The table open_projects will
receive the updates, and the table project_updates
will not be modified. A row is considered to already exist if the project number column (pno) is the same in both tables.
The rest of the MERGE statement is an update clause, with
the new DELETE WHERE syntax in place.
WHEN MATCHED THEN
UPDATE SET pctdone = pu.pctdone,
empno = pu.empno
WHERE pu.action = 'D';
Listing B shows
the tables before and after the MERGE statement runs.
The first transaction is
a change (action = ‘C’) to project number 10. The percentage done is updated
from 0 to 50, and the project is transferred to employee number 214. The second
transaction deletes project number 20; the “after” listing shows that
it is gone. The project_updates table is unchanged.
This example also shows how the clauses are optional; there is no INSERT clause
(“WHEN NOT MATCHED”) in the MERGE statement.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.