merge into用法

參考:http://hi.baidu.com/yafeiie/blog/item/540112a862c9b0b7cb130c19.html

oracle merge into 的用法详解+实例

2008-08-06 22:17

作用：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 (fp0,fp1,fp2,fp3,org_severity,redefine_severity,event_time ,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效率非常高。