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