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','yyyy-mm-dd') ,
     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','yyyy-mm-dd'),to_date('2017-12-12','yyyy-mm-dd'),'巳',
     '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);