MERGE table, do nothing when matched
For your case, no need to use the part:
when matched then update ...
( using when matched then update set a.id = a.id
is accepted(Oracle doesn't hurl) but has no impact, so, such a usage is redundant, because you don't want to change anything for the matching case. )
If you wanted to change, then add
when matched then update set a.id = b.id
before
when not matched then insert...
( e.g.Oracle supports
when matched then update
syntax. Refer the Demo below )
Go on with the following for the current case :
SQL> create table domains( id int, name varchar2(50), code varchar2(50), description varchar2(50));
SQL> insert into domains values(1,'Domain A','D.A.','This is Domain A');
SQL> merge into domains A
using
(select 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description from domains) b
on ( a.name = b.name )
when not matched then insert( a.id, a.name, a.code, a.description )
values( b.id, b.name, b.code, b.description );
SQL> select * from domains;
ID NAME CODE DESCRIPTION
-- -------- ----- ----------------
1 Domain A D.A. This is Domain A
SQL> delete domains;
SQL> insert into domains values(1,'Domain A','D.A.','This is Domain A');
-- we're deleting and inserting the same row again
SQL> merge into domains A
using
(select 2 id, 'Domain B' name, 'D.B.' code, 'This is Domain B' description from domains) b
on ( a.name = b.name )
when not matched then insert( a.id, a.name, a.code, a.description )
values( b.id, b.name, b.code, b.description );
ID NAME CODE DESCRIPTION
-- -------- ----- ----------------
1 Domain A D.A. This is Domain A
2 Domain B D.B. This is Domain B
Demo
Oracle SQL syntax supports not having any when matched then update
clause.
drop table ft purge;
create table ft (c1 number, c2 varchar2(10));
drop table ld purge;
create table ld (c1 number, c2 varchar2(10));
insert into ft values (1,'a');
insert into ld values (1,'b');
insert into ld values (2,'c');
commit;
merge into ft
using ld
on (ft.c1 = ld.c1)
when not matched then
insert (c1,c2) values (ld.c1,ld.c2);
select * from ft;
C1 C2
--- ---
1 a
2 c
2 rows selected.