Introduction the Oracle MERGE statement
MERGE INTO [target_table] A USING [source_table/sql] B ON ([conditional expression] and [...]...) WHEN MATCHED THEN [UPDATE/INSERT/DELETE statement] WHEN NOT MATCHED THEN [INSERT/UPDATE/DELETE statement]
First, specify the target table (target_table) which you want to update or insert into in the INTO clause.
Second, specify the source of data (source_table) to be updated or inserted in the USING clause.
Third, specify the search condition upon which the merge operation either updates or inserts in the ON clause.
For each row in the target table, Oracle evaluates the search condition:
- If the result is true, then Oracle updates the row with the corresponding data from the source table.
- In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.
The MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation. Because the MERGE is a deterministic statement, you cannot update the same row of the target table multiple times in the same MERGE statement.
You can add an optional DELETE WHERE clause to the MATCHED clause to clean up after a merge operation. The DELETE clause deletes only the rows in the target table that match both ON and DELETE WHERE clauses.
Test Example:
-- Drop Table
drop table TSrc purge;
drop table TDest purge;
-- Src & Dest Table
create table TSrc (c1 number, c2 varchar2(200), c3 date);
create table TDest (c1 number, c2 varchar2(200));
-- Insert Src Table
insert into TSrc values(1, 'ABCDE' , to_date('2022-12-06 11:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(2, 'FGHIJ' , to_date('2022-12-07 12:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(3, 'KLMNO' , to_date('2022-12-08 13:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(4, 'PQRST' , to_date('2022-12-09 14:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(5, 'UVWXY' , to_date('2022-12-10 15:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(6, 'Z1234' , to_date('2022-12-11 16:33','yyyy-mm-dd hh24:mi'));
insert into TSrc values(7, '56789' , to_date('2022-12-12 17:33','yyyy-mm-dd hh24:mi'));
-- Insert Dest Table
insert into TDest values(1, 'abcde');
insert into TDest values(3, 'klmno');
commit;
Test Case1: Merge Normal
begin
merge into TDest a
using TSrc b
on (a.c1 = b.c1)
when MATCHED then
update set
a.c2 = b.c2
when NOT MATCHED then
insert (c1, c2) values (b.c1, b.c2);
end;
/
SQL> select * from tdest order by 1;
C1 C2
---------- --------------------------------------------------
1 ABCDE
2 FGHIJ
3 KLMNO
4 PQRST
5 UVWXY
6 Z1234
7 56789
Test Case2: Merge Only MATCHED
begin
merge into TDest a
using TSrc b
on (a.c1 = b.c1)
when MATCHED then
update set
a.c2 = b.c2;
end;
/
SQL> select * from tdest order by 1;
C1 C2
---------- --------------------------------------------------
1 ABCDE
3 KLMNO
Test Case3: Merge Only NOT MATCHED
begin
merge into TDest a
using TSrc b
on (a.c1 = b.c1)
when NOT MATCHED then
insert (c1, c2) values (b.c1, b.c2);
end;
/
SQL> select * from tdest order by 1;
C1 C2
---------- --------------------------------------------------
1 abcde
2 FGHIJ
3 klmno
4 PQRST
5 UVWXY
6 Z1234
7 56789
Test Case4: Merge from special column
begin
merge into TDest a
using (select b.c1,b.c3 from TSrc b) b
on (a.c1 = b.c1)
when MATCHED then
update set
a.c2 = to_char(b.c3,'yyyy-mm-dd hh24:mi')
when NOT MATCHED then
insert (c1, c2) values (b.c1, to_char(b.c3,'yyyy-mm-dd hh24:mi'));
end;
/
SQL> select * from tdest order by 1;
C1 C2
---------- --------------------------------------------------
1 2022-12-06 11:33
2 2022-12-07 12:33
3 2022-12-08 13:33
4 2022-12-09 14:33
5 2022-12-10 15:33
6 2022-12-11 16:33
7 2022-12-12 17:33
Ref:
0 留言