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