日期:2014-05-16  浏览次数:20625 次

oracle merge into 的用法详解+实例

?

作用:merge into 解决用B表更新A表数据,如果A表中没有,则把B表的数据插入A表;

语法:

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效率非常高。

(转载请注明出处:[url=http://www.live588.org]淘金盈[/url] [url=http://www.tswa.org]博彩通[/url] ?[url=http://www.nsdm.org]澳门博彩[/url] ?[url=http://www.lixx.org]博彩网[/url][url=http://www.uvip.org]e世博[/url][url=http://www.10086money.com]时尚资讯[/url][url=http://262002954-qq-com.iteye.com]flex[/url][url=http://flexflex.iteye.com]flex[/url])

?