Oracle使用Merge into 注意事项

前端之家收集整理的这篇文章主要介绍了Oracle使用Merge into 注意事项前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

使用merge into时注意:

1、在update子句中的字段,不可以出现在on子句的条件中

2、update和insert子句中可以不用写tablename,系统将使用merg into 后的tablename

3、在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数


--一条数据时,通过构造dual来进行判断

MERGE INTO sc_wlhz T1 
 USING (SELECT 'gjsAAAAAsx43xn37' AS fsupplierid,'JAH07007' AS fmateielcode FROM dual) T2 
 ON ( T1.fsupplierid=T2.fsupplierid and T1.fmateielcode=T2.fmateielcode )
  WHEN MATCHED THEN  
    UPDATE  SET  T1.FSUPPLIER='长沙欧式橡塑密封件有限公司',T1.FCONTRACTSTARTDATE=to_date('2016-12-12','yyyy-mm-dd'),T1.FCONTRACTENDDATE=to_date('2017-12-12',T1.FBRAND='巳',T1.FPRICE='378',T1.FMATERIELNAME='工业脚轮',T1.FMODELS='定向脚轮 尼龙轮胎 载荷60Kg D=80',T1.FUNIT='个'
  WHEN NOT MATCHED THEN 
   INSERT (FID,FSUPPLIERID,FSUPPLIER,FCONTRACTSTARTDATE,FCONTRACTENDDATE,FBRAND,FPRICE,FMATEIELCODE,FMATERIELNAME,FMODELS,FUNIT,FSTATUS)
     VALUES(SYS_GUID(),'gjsAAAAAsx43xn37','长沙欧式橡塑密封件有限公司',to_date('2016-12-12',to_date('2017-12-12','巳','378','JAH07007','工业脚轮','定向脚轮 尼龙轮胎 载荷60Kg D=80','个',0);


--根据表或子查询数据进行判断

--利用表 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表中没有该条件的数据就插入。

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);

猜你在找的Oracle相关文章