Delete rows during updates using Oracle 10g's MERGE statement

by Bob Watkins | Feb 20, 2007 2:13:00 PM

Tags: Storage, Databases, Listing B, Bob Watkins, Oracle Application Server 10g, clause, Oracle Corp., Oracle Tips Newsletter

Takeaway: In Oracle 10g Release 1, the MERGE statement gained the ability to delete rows, as well as update and insert them. This Oracle tip shows you how it works.

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 updated.

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 using MERGE.

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

USING project_updatespu

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

DELETE
       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.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.