MERGE表，匹配时不执行任何操作 [英] MERGE table, do nothing when matched

问题描述

我有2种不同模式的表DOMAINS，其中的列为ID，NAME，CODE，DESCRIPTION.

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

对于新模式中存在的任何NAME，它应使用现有的ID，而不进行任何合并；对于这些新的NAME记录，它应该与旧模式中的ID一起插入.

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);

如何解释上述查询中do nothing的部分?

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

推荐答案

对于您的情况，无需使用该部分:

For your case, no need to use the part:

when matched then update ...

(使用when matched then update set a.id = a.id的被接受(Oracle不会抛出错误)，但没有影响，因此，这种用法是多余的，因为您不想为匹配的情况更改任何内容.)

如果要更改，请添加

If you wanted to change, then add

when matched then update set a.id = b.id

在when not matched then insert...

(例如，Oracle支持when matched then update语法.请参考 演示下方)

( e.g.Oracle supports when matched then update syntax. Refer the Demo below )

针对当前情况继续进行以下操作:

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

演示

这篇关于MERGE表，匹配时不执行任何操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！