现在一个项目把mysql的数据转移到oracle,程序需要修改:
其中有个sql语句用到了mysql的on duplicate key update,如下所示:
String sql = "insert into alarm(NEID,MachineID,AlarmID,AlarmLevel,AlarmContent,AlarmDate,AlarmType,AlarmCount) values(?,?,?,?,?,?,?,?) on duplicate key update " +
"AlarmLevel=?,AlarmContent=?,AlarmDate=?,AlarmType=?,AlarmCount=AlarmCount+1,LastAlarmDate=AlarmDate";请问,这条语句如何用oracle的等效语法重写?
我查了一下,好像有个merge能做,但是都是从另一个表导入数据到一个表。请高人指点指点吧。谢谢啦。
我查了一下on duplicate key update,可以根据主键,也可以根据表上面的唯一索引匹配。我这里NEID,MachineID,AlarmID,AlarmLevel这几个字段构成了唯一索引,merge如何按照表中唯一索引来匹配是insert还是update呢?
其中有个sql语句用到了mysql的on duplicate key update,如下所示:
String sql = "insert into alarm(NEID,MachineID,AlarmID,AlarmLevel,AlarmContent,AlarmDate,AlarmType,AlarmCount) values(?,?,?,?,?,?,?,?) on duplicate key update " +
"AlarmLevel=?,AlarmContent=?,AlarmDate=?,AlarmType=?,AlarmCount=AlarmCount+1,LastAlarmDate=AlarmDate";请问,这条语句如何用oracle的等效语法重写?
我查了一下,好像有个merge能做,但是都是从另一个表导入数据到一个表。请高人指点指点吧。谢谢啦。
我查了一下on duplicate key update,可以根据主键,也可以根据表上面的唯一索引匹配。我这里NEID,MachineID,AlarmID,AlarmLevel这几个字段构成了唯一索引,merge如何按照表中唯一索引来匹配是insert还是update呢?
update_statement
if sql%nofound then
insert_statement
end if
merge into alarm
using (select '?' NEID,'?' MachineID,'?' AlarmID,'?' AlarmLevel from dual) t
on(alarm.NEID=t.NEID and alarm.MachineID=t.MachineID
and alarm.AlarmID=t.AlarmID and alarm.AlarmLevel=t.AlarmLevel)
when not matched then
insert
values (t.NEID ,t.MachineID,t.AlarmID,t.AlarmLevel);
when matched then
update
set AlarmContent=?,AlarmDate=?,AlarmType=?,AlarmCount=AlarmCount+1,LastAlarmDate=AlarmDate
MERGE INTO ALARM a1 Using (SELECT * from ALARM WHERE NEID=100 and ALARMID=10030006 and MACHINEID=16189 and SPECIFICID=0) a2
ON (a1.NEID=a2.NEID and a1.ALARMID=a2.ALARMID and a1.MACHINEID=a2.MACHINEID and a1.SPECIFICID=a2.SPECIFICID)
WHEN MATCHED THEN
UPDATE SET a1.ALARMTYPE=3 WHERE a1.NEID=100 and a1.ALARMID=10030006 and a1.MACHINEID=16189 and a1.SPECIFICID=0
WHEN NOT MATCHED THEN
INSERT VALUES (3362631,100,10030006,16189,1,'Alarm Merge Testing',SYSDATE,0,9999,SYSDATE,6)其中NEID、ALARMID、MACHINEID和SPECIFICID四个字段建立了唯一索引,因此在语句中进行唯一索引重复的判断。测试情况是如果插入数据的四个字段与数据库表中现有四个字段值相等,原表中的对应记录更新成功。但是如果不相等,不会执行插入语句,每次的结果都是:受影响的行: 0 时间: 0.000ms。 很奇怪,不就两种情况吗?match和not match,为什么not match的情况下不执行insert呢?期待高手的帮助,谢谢!
MERGE INTO NMS.ALARM a1 Using (SELECT NEID,ALARMID,MACHINEID,SPECIFICID from NMS.ALARM
union select 310,26883207,16189,0 from dual) a2
ON (a1.NEID=a2.NEID and a1.ALARMID=a2.ALARMID and a1.MACHINEID=a2.MACHINEID and a1.SPECIFICID=a2.SPECIFICID)
WHEN MATCHED THEN
UPDATE SET a1.ALARMTYPE=8 WHERE a1.NEID=310 and a1.ALARMID=26883207 and a1.MACHINEID=16189 and a1.SPECIFICID=0
WHEN NOT MATCHED THEN
INSERT (ALARMSEQ,NEID,ALARMID,MACHINEID,ALARMLEVEL,ALARMCONTENT,ALARMDATE,SPECIFICID,ALARMCOUNT,LASTALARMDATE,ALARMTYPE)
VALUES (3362638,310,26883207,16189,2,'Alarm Merge Testing!',SYSDATE,0,9999,SYSDATE,6);
MERGE INTO ALARM a1 Using (SELECT 310 NEID,26883208 ALARMID,16189 MACHINEID,0 SPECIFICID from dual) a2
ON (a1.NEID=a2.NEID and a1.ALARMID=a2.ALARMID and a1.MACHINEID=a2.MACHINEID and a1.SPECIFICID=a2.SPECIFICID)
WHEN MATCHED THEN
UPDATE SET a1.ALARMTYPE=8 WHERE a1.NEID=310 and a1.ALARMID=26883208 and a1.MACHINEID=16189 and a1.SPECIFICID=0
WHEN NOT MATCHED THEN
INSERT (ALARMSEQ,NEID,ALARMID,MACHINEID,ALARMLEVEL,ALARMCONTENT,ALARMDATE,SPECIFICID,ALARMCOUNT,LASTALARMDATE,ALARMTYPE)
VALUES (3362639,310,26883208,16189,2,'Alarm Merge Testing!!!!!!',SYSDATE,0,9999,SYSDATE,6);