MERGE table, do nothing when matched

Question

I have a table DOMAINS in 2 different schemas with columns ID, NAME,CODE,DESCRIPTION.

For any NAME exist in new schema, it should use existing ID without any merge; for those new NAME records, it should insert with ID from old schema.

MERGE INTO DOMAINS A
USING (SELECT ID,NAME,CODE,DESCRIPTION FROM <Old Schema 6.1>.DOMAINS@DB_MIG_61_TO_74) B
ON(A.NAME = B.NAME)
WHEN MATCHED **<do nothing>**
WHEN NOT MATCHED THEN INSERT(A.ID,A.NAME,A.CODE,A.DESCRIPTION) 
VALUES(B.ID,B.NAME,B.CODE,B.DESCRIPTION);

How can i intepret the portion of do nothing in above query?

Answer 1

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.

Answer 2

As an example :

WHEN MATCHED THEN UPDATE SET A.ID = A.ID

portion may be used for nothing to be changed.

CREATE TABLE DOMAINS(ID int,NAME varchar2(50),CODE varchar2(50),DESCRIPTION varchar2(50));
INSERT INTO DOMAINS values(1,'Domain A','D A','This is Domain A');

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 MATCHED THEN UPDATE SET A.ID = A.ID
 WHEN NOT MATCHED THEN INSERT(A.ID, A.NAME, A.CODE, A.DESCRIPTION)
                       VALUES(B.ID, B.NAME, B.CODE, B.DESCRIPTION);

SELECT * FROM DOMAINS;

ID  NAME        CODE    DESCRIPTION
--  --------   -----  ----------------
1   Domain A    D A   This is Domain A

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 MATCHED THEN UPDATE SET A.ID = A.ID
 WHEN NOT MATCHED THEN INSERT(A.ID, A.NAME, A.CODE, A.DESCRIPTION) 
                       VALUES(B.ID, B.NAME, B.CODE, B.DESCRIPTION);

SELECT * FROM DOMAINS;

ID  NAME        CODE    DESCRIPTION
--  --------   -----  ----------------
1   Domain A    D A   This is Domain A
2   Domain B    D B   This is Domain B

SQL Fiddle Demo