-- oracle
merge into BASE_EXT_DICT t
using (select LOWER(word) word from base_negative_word where isvalid = #{isvalid} ) s
on (s.word = t.ext_word and t.ext_type =1 and t.isvalid = 1 )
when matched then
UPDATE SET t.updatetime = SYSDATE
when not matched then
insert
(id, ext_word, ext_type,isvalid,create_user,createtime,update_user,updatetime,remark)
values
(SEQ_BASE_EXT_DICT.nextval, s.word, #{extType},#{isvalid},#{createUser},#{createTime},#{updateUser},#{updateTime},#{remark})
-- 改造成mysql
-- BASE_EXT_DICT_TEMP：扩展词临时表（实验表）
INSERT INTO BASE_EXT_DICT_TEMP (EXT_WORD, EXT_TYPE, ISVALID, CREATE_USER, CREATETIME, UPDATE_USER, UPDATETIME, REMARK)
-- 要插入字段的值
select word as EXT_WORD, '1' as EXT_TYPE,'1' as ISVALID, 'xuxi' as CREATE_USER, str_to_date('2020-07-27','%Y-%m-%d') as CREATETIME,'xuxi' as UPDATE_USER,str_to_date('2020-07-27','%Y-%m-%d') UPDATETIME,'REMARK' as REMARK from base_negative_word
ON DUPLICATE KEY UPDATE
UPDATETIME = str_to_date('2020-07-29','%Y-%m-%d') -- 要更改的字段的值
-- 参考一个简单的语句
-- mysql
INSERT INTO BASE_EXT_DICT_TEMP (EXT_WORD, EXT_TYPE, ISVALID, CREATE_USER, CREATETIME, UPDATE_USER, UPDATETIME, REMARK)
-- 要插入字段的值
select EXT_WORD, EXT_TYPE, ISVALID, CREATE_USER, CREATETIME, UPDATE_USER, UPDATETIME, REMARK from BASE_EXT_DICT_TEMP where id =22
ON DUPLICATE KEY UPDATE
UPDATETIME = str_to_date('2020-07-30','%Y-%m-%d') -- 要更改的字段的值
条件就是 : 符合索引(唯一索引) , 不用写出来。
借鉴： https://www.cnblogs.com/lijingang/p/10382948.html