merge into 缺失using关键字
今天在做项目的时候,使用merge into总是报错缺失using关键字,可是我SQL里面明明就写了using关键字,找了半天错误,发现了两个常见错误,在此记录一下。
修改前错误代码
merge into PAY_MCT_WHITE as pp
using (select #{pay_mct_white.mctId,jdbcType=VARCHAR} as mct_id,#{pay_mct_white.bankId,jdbcType=VARCHAR} as bank_id,
#{pay_mct_white.flag,jdbcType=VARCHAR} as flag,#{pay_mct_white.createTime,jdbcType=VARCHAR} as create_time,
#{pay_mct_white.createUser,jdbcType=VARCHAR} as create_user,#{pay_mct_white.updateTime,jdbcType=VARCHAR} as update_time,
#{pay_mct_white.updateUser,jdbcType=VARCHAR} as update_user
from dual)as tt
on (pp.MCT_ID=tt.mct_id)
when not matched then
insert(MCT_ID,BANK_ID,FLAG,CREATE_USER,CREATE_TIME,UPDATE_USER,UPDATE_TIME)
values(#{pay_mct_white.mctId,jdbcType=VARCHAR},#{pay_mct_white.bankId,jdbcType=VARCHAR},#{pay_mct_white.flag,jdbcType=VARCHAR}
,#{pay_mct_white.createUser,jdbcType=VARCHAR},#{pay_mct_white.createTime,jdbcType=VARCHAR},
#{pay_mct_white.updateUser,jdbcType=VARCHAR},#{pay_mct_white.updateTime,jdbcType=VARCHAR})
when matched then
update set flag=#{pay_mct_white.flag}
where mct_id=#{pay_mct_white.mctId}
修改后正确代码
merge into PAY_MCT_WHITE pp
using (select #{pay_mct_white.mctId,jdbcType=VARCHAR} as mct_id,#{pay_mct_white.bankId,jdbcType=VARCHAR} as bank_id,
#{pay_mct_white.flag,jdbcType=VARCHAR} as flag,#{pay_mct_white.createTime,jdbcType=VARCHAR} as create_time,
#{pay_mct_white.createUser,jdbcType=VARCHAR} as create_user,#{pay_mct_white.updateTime,jdbcType=VARCHAR} as update_time,
#{pay_mct_white.updateUser,jdbcType=VARCHAR} as update_user
from dual) tt
on (pp.MCT_ID=tt.mct_id)
when matched then
update set flag=#{pay_mct_white.flag}
where mct_id=#{pay_mct_white.mctId}
when not matched then
insert(MCT_ID,BANK_ID,FLAG,CREATE_USER,CREATE_TIME,UPDATE_USER,UPDATE_TIME)
values(#{pay_mct_white.mctId,jdbcType=VARCHAR},#{pay_mct_white.bankId,jdbcType=VARCHAR},#{pay_mct_white.flag,jdbcType=VARCHAR}
,#{pay_mct_white.createUser,jdbcType=VARCHAR},#{pay_mct_white.createTime,jdbcType=VARCHAR},
#{pay_mct_white.updateUser,jdbcType=VARCHAR},#{pay_mct_white.updateTime,jdbcType=VARCHAR})
错误总结
1、使用merge into的时候,推荐应该先把匹配的数据进行修改,然后没有匹配的数据才插入,不按照这样的顺序写的话,大部分情况也不会报错。但是咱们程序员必须严谨,所以推荐先修改数据,后插入数据。
2、在给表赋值别名的时候,不要使用AS关键字来给表赋值别名,直接用空一格的方式来给表赋值别名。(本人犯的主要错误就是这里)