应用场景: 处理业务数据时,如果数据库中存在这条记录则更新,如果不存在就更新。
实现SQL:(使用语oracle9i 之后的版本)
- 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 ! ]
说明:
using:update/insert 的原数据,就是使用这些查到的数据进行更新或者插入操作的。所以说必须保证能够查出数据。
on:判断条件,判断外层表记录和using查出来的记录。
Merge: merge是用来更新整张表用得,使用时要小心。
使用例子:
- 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_servertify,条件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3 如果tfa_alarm_act_nms表中没有该条件的数据就插入。
本文参考:http://azrael6619.iteye.com/blog/759678
http://blog.csdn.net/yuzhic/article/details/1896878
无意冒犯原作者,只是自己整理下,方便自己查看!谢谢!!