oracle MERGE 表，匹配时什么都不做
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49848995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MERGE table, do nothing when matched
提问by user2102665
I have a table DOMAINSin 2 different schemas with columns ID, NAME,CODE,DESCRIPTION.
我有一个包含DOMAINS2 个不同模式的表ID，其中包含、NAME、CODE、DESCRIPTION。
For any NAMEexist in new schema, it should use existing IDwithout any merge; for those new NAMErecords, it should insert with IDfrom old schema.
对于NAME新模式中的任何存在，它应该使用现有ID而不进行任何合并；对于那些新NAME记录，它应该ID从旧模式插入。
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 nothingin above query?
我如何解释do nothing上述查询中的部分？
回答by Barbaros ?zhan
For your case, no need to use the part:
对于您的情况，无需使用该部分：
when matched then update ...
when matched then update ...
( using when matched then update set a.id = a.idis 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.)
（usingwhen matched then update set a.id = a.id被接受（Oracle 不投掷）但没有影响，因此，这种用法是多余的，因为您不想为匹配的情况更改任何内容。）
If you wanted to change, then add
when matched then update set a.id = b.idbefore
when not matched then insert...( e.g.Oracle supports
when matched then updatesyntax. Refer the Demobelow)
如果你想改变，然后添加
when matched then update set a.id = b.id前
when not matched then insert...（例如Oracle支持
when matched then update语法。参考下面的Demo）
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
回答by Vijay Balebail
Oracle SQL syntax supports not having any when matched then updateclause.
Oracle SQL 语法支持没有任何when matched then update子句。
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.
