Updating data rows with Oracle 10gMERGE statements

The merge statements introduced in Oracle 9i R2 are often referred to as "update Inserts" (Upsert), because the merge allows you to update and insert (insert) rows of data in the same step, for extraction, Converting and loading types of applications can save a lot of valuable time, such as loading data into the Data Warehouse, data rows that are not in the data warehouse can be plugged into the data warehouse, and existing data rows are also updated.

When the merge statement is introduced, you need to use both an update and an INSERT statement, and the order is fixed (using the UPDATE statement first and then the INSERT statement). If you only need to use one of these sentences, you only need to use an existing INSERT or UPDATE statement without having to use the merge statement, and deleting the data can use the DELETE statement.

In Oracle 10g R1, the merge statement has changed, and the update or INSERT statement is no longer required, but optional, you can both use it and neither, and the UPDATE statement has the function of delete. You can upgrade existing valid records and clean obsolete records in the same step.

List A creates a table that lists the existing items: project number, title, start date, progress percentage, and employee response to the project, and also creates a transaction table that uses the merge statement for the upgrade batch.

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
(A, ' Inventory servers ', ' 08-jan-07 ', 0, 206);
INSERT into Open_projects VALUES
(, ' Upgrade Oracle on SRV01 ', ' 15-jan-07 ', 0, 206);
INSERT into Open_projects VALUES
(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 ',, NULL, NULL);
COMMIT;

List A

A typical merge statement starts with an upgrade from the identification table and tests the existing records for filtering:

MERGE into Open_projects op
USING PROJECT_UPDATESPU
On (op.pno = pu.pno)
...

The table open_projects will accept the updated data, and the Project_updates table will not change, and if the project number (pno) is the same in all two tables, then the data row is considered the same.

The remaining part of the merge statement is the UPDATE statement and the delete where syntax.

...
When matched THEN
UPDATE SET PctDone = Pu.pctdone,
Empno = Pu.empno
DELETE
WHERE pu.action = ' D ';

List B shows the table before and after the merge statement runs.

Sql> @mergedel_b
PNO TITLE startdate PctDone EMPNO
---------- ---------------------------------------- --------- ---------- ----------
Ten Inventory servers 08-jan-07 0 206
Upgrade Oracle on SRV01 15-jan-07 0 206
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
---------- ---------------------------------------- --------- ---------- ----------
Ten Inventory servers 08-jan-07 50 214
Conduct skills Assessment 22-jan-07 0 210
A PNO PctDone EMPNO
- ---------- ---------- ----------
C 10 50 214
D 20
Sql> Spool Off

List B

The first transaction changed item 10th (operation ' C '), the proportion of project completion has changed from 0 to 50, and the number of project staff has become 214; the second transaction outputs the 20th item, and the "then" list shows the deleted state, and the Project_updates table does not change. This example shows that these statements are not necessary and that the INSERT statement is not required in the merge statement.