How to use MERGE in Oracle

MERGE is used to select rows from one or more sources, and update or insert into a table or view. You cannot update the same row of the target table multiple times in the same MERGE statement.

Syntax:

MERGE INTO <table/view>
USING <table/view/subquery> ON (condition)

--update

WHEN MATCHED THEN
UPDATE SET column = (value/expr),..
WHERE (condition) | DELETE WHERE (condition)

--insert

WHEN NOT MATCHED THEN
INSERT (column,..) VALUES (value/expr,..)
WHERE (condition)

--error log

LOG ERRORS INTO <table>

If the ON clause condition is true

then UPDATE section gets executed.

else

INSERT section gets executed.

Restrictions on the merge update:

1. You cannot update a column that is referenced in the ON condition clause.

2. Cannot specify DEFAULT when updating a view.

See the example code below.

Create a target table called TEST1 and inset some test data

CREATE TABLE test1 (
co1 VARCHAR2(3),
co2 VARCHAR2(3),
co3 VARCHAR2(3),
co4 NUMBER );

ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY(co1);

INSERT INTO test1 VALUES('1', 'val', 'sd1', 100);
INSERT INTO test1 VALUES('2', 'va2', 'sd2', 100);
INSERT INTO test1 VALUES('3', 'va3', 'sd3', 100);
INSERT INTO test1 VALUES('4', 'va4', 'sd4', NULL);
INSERT INTO test1 VALUES('5', 'va5', 'sd5', 100);

commit;


Create a target table called TEST2 and inset some test data

CREATE TABLE test2 (
a1 VARCHAR2(3),
a2 NUMBER );

ALTER TABLE test2 ADD CONSTRAINT test2_pk PRIMARY KEY(a1);

INSERT INTO test2 VALUES('1', 10);
INSERT INTO test2 VALUES('3', 20);
INSERT INTO test2 VALUES('6', 30);
INSERT INTO test2 VALUES('10', 40);
INSERT INTO test2 VALUES('15', 50);
INSERT INTO test2 VALUES('2', -10);

commit;

Now by using the MERGE statement we are going to update/delete or either insert records to the target table(test1) by using the source (test2).

1. MERGE INTO test1 a
2. USING (select a1, a2
3. from test2 ) b
4. ON (a.co1 = b.a1)
5. WHEN MATCHED THEN
6. update set a.co4 = a.co4 * b.a2,
7. a.co2 = b.a2
8. where co3 = \'sd3\'
9. delete where co3 = \'sd3\'
10. WHEN NOT MATCHED THEN
11. insert (a.co1, a.co2, a.co3, a.co4) values
12. (b.a1, null, null, b.a2);


line 2. uses a subquery to select the source records.
line 4. ON clause specify the condition. This condition controls the MERGE to perform update or insert. If true then update else insert.
line 6., 7. updates the columns by using the source column data.
line 9. deletes the target row after updations when the delete condition satisfied. This delete clause evaluates the updated value not the original values.
This statement executes only when ON clause condition (line 4) returns true.
line 10.When ON clause is false then this insert statement is executed.