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