oracle MERGE表格,匹配时不执行任何操作

flseospp  于 2022-12-29  发布在  Oracle
关注(0)|答案(3)|浏览(214)

我有一个表DOMAINS,它有两个不同的模式,列分别为IDNAMECODEDESCRIPTION
对于新模式中存在的任何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 );
  • 如何解释上述查询中do nothing的部分?*
ctrmrzij

ctrmrzij1#

对于您的情况,无需使用部件:
第一个月
(* 使用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语法。请参阅下面的演示 *)

  • 对当前案例继续执行以下操作:*
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 );

SQL> 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

Demo

顺便说一句USING关键字后面的部分不必是***子查询***,而是***表***或***视图***。针对当前案例进行评估后:

MERGE INTO domains ds       --> "ds" : "domains" source
     USING v_domains dt     --> "dt" : "domains" target
        ON ( ds.name = dt.name )
      WHEN NOT MATCHED THEN INSERT( ds.id, ds.name, ds.code, ds.description ) 
                            VALUES( dt.id, dt.name, dt.code, dt.description )

在创建**v_domains**view through后,可以像上面的语句一样使用

CREATE OR REPLACE VIEW v_domains AS
SELECT 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description 
  FROM domains
jdg4fx2g

jdg4fx2g2#

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.
jk9hmnmh

jk9hmnmh3#

为什么不使用
并入A
使用B
在...
当不匹配时
...
我已经验证过了。

相关问题