Oracle Merge 单表操作更新安插

Oracle Merge 单表操作更新插入

MERGE INTO WARN_BOX_COLLECT a?

USING(select ID from (

select ID from WARN_BOX_COLLECT where ID = '02-23-34-BX-55-00-280001'

union all

select NULL ID from dual

) where rownum=1) b

ON(b.ID = a.ID)

WHEN MATCHED THEN

UPDATE

SET a.WARN_TIMES = a.WARN_TIMES + 1, a.WARN_LAST_TIME = SYSDATE

WHEN NOT MATCHED THEN

INSERT

(ID,BOX_MAC,WARN_TYPE,WARN_FIRST_TIME,WARN_LAST_TIME,WARN_TIMES,BOX_WARN_NUM)

VALUES('02-23-34-BX-55-00-280001','02-23-34-BX-55-00-28','000',SYSDATE,SYSDATE,1,1);