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>
原文地址：https://blog.csdn.net/weixin_69965075/article/details/136256945
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！