oracle中merge into..using..on..when..when..用法

发布时间：2020-12-12 14:59:52 所属栏目：百科来源：网络整理

导读：语法： MERGE INTO [your table-name] [rename your table here]USING ( [write your query here] )[rename your query-sql and using just like a table]ON ([conditional expression here] AND [...]...)WHEN MATHED THEN [here you can execute some updat

语法：

MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]

实例：

merge into tfa_alarm_act_nms a     
using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY     
from tfa_alarm_status) b     
on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3)     
when matched then update set a.redefine_severity=b.redefine_severity     
when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,a.event_time,a.int_id)     
values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778);

作用：利用表 tfa_alarm_status跟新表tfa_alarm_act_nms 的b.redefine_severity，条件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3，如果tfa_alarm_act_nms表中没有该条件的数据就插入。

如果你的数据量很大，此sql效率非常高。

转自：http://azrael6619.iteye.com/blog/759678

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!