问题描述
我有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不会抛出错误),但没有影响,因此,这种用法是多余的,因为您不想为匹配的情况更改任何内容.)
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表,匹配时不执行任何操作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!