create table tq84_merge_source (
id number,
val varchar2(10)
);
create table tq84_merge_destination (
id number,
val_1 varchar2(10),
val_2 varchar2(10)
);
begin
insert into tq84_merge_source values ( 1, 'one' );
insert into tq84_merge_source values ( 2, 'two' );
insert into tq84_merge_source values ( 3, 'three' );
insert into tq84_merge_source values ( 4, 'four' );
insert into tq84_merge_source values ( 5, 'five' );
insert into tq84_merge_source values ( 6, 'six' );
insert into tq84_merge_source values ( 7, 'seven' );
insert into tq84_merge_source values ( 8, 'eight' );
insert into tq84_merge_source values ( 9, 'nine' );
insert into tq84_merge_source values (10, 'ten' );
insert into tq84_merge_source values (20, 'twenty');
insert into tq84_merge_destination values ( 1, '***' , 'abc');
insert into tq84_merge_destination values ( 6, 'six' , 'def');
insert into tq84_merge_destination values ( 8, '!!!' , 'ghi');
insert into tq84_merge_destination values (14, 'XIV' , 'jlm'); -- 14 is not in source
commit;
end;
/
merge into tq84_merge_destination dst
using tq84_merge_source src
on ( dst.id = src.id )
when matched then
update set val_1 = src.val
when not matched then
insert ( id, val_1) values
(src.id, src.val );
select * from tq84_merge_destination order by id;
--
--
-- ID VAL_1 VAL_2
-- ---------- ---------- ----------
-- 1 one abc
-- 2 two
-- 3 three
-- 4 four
-- 5 five
-- 6 six def
-- 7 seven
-- 8 eight ghi
-- 9 nine
-- 10 ten
-- 14 XIV jlm
-- 20 twenty
drop table tq84_merge_source;
drop table tq84_merge_destination;
Merge on condition
create table tq84_merge_on_conditon (
id number primary key,
updateable number not null check (updateable in (0,1)),
text varchar2(20)
);
insert into tq84_merge_on_conditon values (1, 1, 'abcdefghij' );
insert into tq84_merge_on_conditon values (2, 1, '1234567890' );
insert into tq84_merge_on_conditon values (3, 0, 'can not be merged');
insert into tq84_merge_on_conditon values (4, 1, '###' );
declare
procedure merge_ (p_id in number, p_text in varchar2) is -- {
begin
merge into tq84_merge_on_conditon t
using (select p_id p_id, p_text p_text from dual) u
on (t.id = u.p_id)
when matched then update set
-- Set the new text only if updateable = 0:
t.text = case when t.updateable = 1 then u.p_text else t.text end
when not matched then insert (
id, updateable, text) values (
u.p_id, 1, u.p_text);
end merge_; -- }
begin
merge_(3, 'should not merge');
merge_(4, 'should merge' );
merge_(5, 'new record' );
end;
/
select * from tq84_merge_on_conditon order by id;
drop table tq84_merge_on_conditon purge;
create table tq84_merge_dest (
id number,
txt varchar2(10),
constraint tq84_merge_dest_pk primary key (id)
);
insert into tq84_merge_dest values (1, 'foo');
insert into tq84_merge_dest values (3, 'bar');
insert into tq84_merge_dest values (5, 'baz');
merge into tq84_merge_dest dest
using (select 1 id, 'one' txt from dual union all
select 2 id, 'two' txt from dual union all
select 4 id, 'four' txt from dual
) src
on (dest.id = src.id)
when matched then
update set txt = src.txt
when not matched then
insert (id, txt) values (src.id, src.txt)
;
merge into tq84_merge_dest dest
using (select 3 id, 'three' txt from dual union all
-- select 6 id, 'six' txt from dual union all -- unique constraint (META.TQ84_MERGE_DEST_PK) violated
select 6 id, 'six' txt from dual
) src
on (dest.id = src.id)
when matched then
update set txt = src.txt
when not matched then
insert (id, txt) values (src.id, src.txt)
;
select * from tq84_merge_dest;
drop table tq84_merge_dest purge;