Set named restore points for easier flashback in Oracle 10gR2

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.

The code below 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.

DROP TABLE open_projects;
DROP TABLE project_updates;
CREATE TABLE open_projects
(pno NUMBER(6) PRIMARY KEY,
title VARCHAR2(40),
startdate DATE,
pctdone NUMBER(3),
empno NUMBER(6)
);
INSERT INTO open_projects VALUES
(10, 'Inventory servers', '08-JAN-07', 0, 206);
INSERT INTO open_projects VALUES
(20, 'Upgrade Oracle on SRV01', '15-JAN-07', 0, 206);
INSERT INTO open_projects VALUES
(30, 'Conduct skills assessment',
'22-JAN-07', 0, 210);
CREATE TABLE project_updates
(action CHAR(1),
pno NUMBER(6),
pctdone NUMBER(3),
empno NUMBER(6)
);
INSERT INTO project_updates VALUES
('C', 10, 50, 214);
INSERT INTO project_updates VALUES
('D', 20, NULL, NULL);
COMMIT;

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';



The code below shows the tables before and after the MERGE statement runs.

SQL> @mergedel_b
PNO TITLE STARTDATE PCTDONE
---------- ---------------------------------------- --------- ----------
EMPNO
---------- 10 Inventory servers 8-JAN-07 0
206
20 Upgrade Oracle on SRV01 15-JAN-07 0
206
30 Conduct skills assessment 22-JAN-07 0
210 A PNO PCTDONE EMPNO
- ---------- ---------- ----------
C 10 50 214
D 20
2 rows merged.
PNO TITLE STARTDATE PCTDONE
---------- ---------------------------------------- --------- ----------
EMPNO
----------
10 Inventory servers 08-JAN-07 50
214 30 Conduct skills assessment 22-JAN-07 0
210 A PNO PCTDONE EMPNO
- ---------- ---------- ----------
C 10 50 214
D 20
SQL> spool off


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.