Oracle中merge into和update的用法

精准匹配的更新语句1

UPDATE WX_USER_Test T SET (T.RCODE,T.branch) = (SELECT T1.RCODE,T1.BRANCH FROM WX_USER_TMP T1 where T.mobile=T1.mobile)

精准匹配的更新语句2

UPDATE WX_USER_Test T SET (T.RCODE,T.branch) = (SELECT T1.RCODE,T1.BRANCH FROM WX_USER_TMP T1 where T.mobile=T1.mobile)
where T.RCODE is null

精准匹配的更新语句3

merge into WX_USER_Test x
using(SELECT T1.RCODE, T1.BRANCH,t.id
FROM WX_USER_TMP T1 inner join WX_USER_Test T on T.mobile = T1.mobile) y
on(x.id=y.id )
when matched then
update set x.RCODE=y.rcode,x.branch=y.branch

Merge into语法详细介绍

merge into 函数是 Oracle 9i 推出的新特性。
其主要功能是合并了 Insert into 语句和 update 语句；
    如果该条记录存在，则执行 update 语句；
    如果该条记录不存在，则执行 insert 语句。

merge into 函数因为只需要一次搜索，所以其效率远高于同时使用 Insert 和 update 语句；
不仅如此，大多数情况下，正确的使用 merge into 语法即时只进行 update 操作或者 insert 操作，效率也会远高于单独使用 insert 或者 update。

Merge into使用

-- merge into 基本语法
MERGE INTO 目标表 A
USING 数据源表 B
ON (A.条件1 = B.条件1 AND A.条件2 = B.条件2)
WHEN MATCHED THEN
  UPDATE
     SET A.字段1 = B.字段1,
     A.字段2 = B.字段2
WHEN NOT MATCHED THEN INSERT VALUES(B.字段1, B.字段2);

 Merge into 注意事项

1、Merge into 中的目标表名在 merge into 关键字后；
2、Merge into 语句中的 update 关键字后不能写表名；
3、Merge into 语句中的 insert 关键字后没有 into 关键字和 表名；

Merge into 实例

SQL> SELECT T.* FROM TEST_MACIDEPART T;
 
DPT_NO               DPT_NAM                                  DPTTYPE DPT_UP               ID_FACTORY
-------------------- ---------------------------------------- ------- -------------------- --------------------
 
SQL>
SQL> MERGE INTO TEST_MACIDEPART A
  2  USING (SELECT '1' DPT_NO,'ABC' DPT_NAM,'D' DPTTYPE,'AAA' DPT_UP,'SS' ID_FACTORY FROM DUAL UNION ALL
  3         SELECT '2' DPT_NO,'FED' DPT_NAM,'B' DPTTYPE, 'ABB' DPT_UP, 'DD' ID_FACTORY  FROM DUAL UNION ALL
  4         SELECT '3' DPT_NO,'SDG' DPT_NAM,'C' DPTTYPE, 'CDS' DPT_UP,'TT' ID_FACTORYFROM FROM DUAL ) B
  5  ON (A.DPT_NO = B.DPT_NO)
  6  WHEN MATCHED THEN
  7    UPDATE
  8       SET A.DPT_NAM    = B.DPT_NAM,
  9           A.DPTTYPE    = B.DPTTYPE,
 10           A.DPT_UP     = B.DPT_UP,
 11           A.ID_FACTORY = B.ID_FACTORY
 12  WHEN NOT MATCHED THEN
 13    INSERT VALUES (B.DPT_NO, B.DPT_NAM, B.DPTTYPE, B.DPT_UP, B.ID_FACTORY);
 
3 rows merged
 
SQL> SELECT T.* FROM TEST_MACIDEPART T;
 
DPT_NO               DPT_NAM                                  DPTTYPE DPT_UP               ID_FACTORY
-------------------- ---------------------------------------- ------- -------------------- --------------------
1                    ABC                                      D       AAA                  SS
2                    FED                                      B       ABB                  DD
3                    SDG                                      C       CDS                  TT
 
SQL>
SQL> MERGE INTO TEST_MACIDEPART A
  2  USING (SELECT '3' DPT_NO,'ZZZ' DPT_NAM,'D' DPTTYPE,'ATT' DPT_UP,'LL' ID_FACTORY FROM DUAL UNION ALL
  3         SELECT '4' DPT_NO,'TEST' DPT_NAM,'M' DPTTYPE, 'ERG' DPT_UP, 'IN' ID_FACTORY  FROM DUAL ) B
  4  ON (A.DPT_NO = B.DPT_NO)
  5  WHEN MATCHED THEN
  6    UPDATE
  7       SET A.DPT_NAM    = B.DPT_NAM,
  8           A.DPTTYPE    = B.DPTTYPE,
  9           A.DPT_UP     = B.DPT_UP,
 10           A.ID_FACTORY = B.ID_FACTORY
 11  WHEN NOT MATCHED THEN
 12    INSERT VALUES (B.DPT_NO, B.DPT_NAM, B.DPTTYPE, B.DPT_UP, B.ID_FACTORY);
 
2 rows merged
 
SQL> SELECT T.* FROM TEST_MACIDEPART T;
 
DPT_NO               DPT_NAM                                  DPTTYPE DPT_UP               ID_FACTORY
-------------------- ---------------------------------------- ------- -------------------- --------------------
1                    ABC                                      D       AAA                  SS
2                    FED                                      B       ABB                  DD
3                    ZZZ                                      D       ATT                  LL
4                    TEST                                     M       ERG                  IN
 
SQL>