参考代码如下:
注意,其中using后的结果集必须用小括号括起来,on后面那个也不能省。
后续操作使用别名处理结果集。
<update id="mergeBank" parameterType="java.util.List">
merge into T_MY_BANK a
using
(
<foreach collection="list" index="index" item="item" open=""
close="" separator="union all">
SELECT
sys_guid() as id,
#{item.bankCode,jdbcType=VARCHAR} as bankCode,
#{item.bankName,jdbcType=VARCHAR} as bankName,
#{item.bankName,jdbcType=VARCHAR} as subBankName,
#{item.bankId,jdbcType=VARCHAR} as bankId,
'1' as isOverseas
FROM dual
</foreach>
) b
on (
a.BANK_ID = b.bankId
)
when
matched then
UPDATE SET
a.BANK_CODE = b.bankCode,
a.BANK_NAME=b.bankName,
a.SUB_BANK_NAME = b.subBankName,
a.UPDATE_DT=sysdate
when
not matched then
INSERT(
a.ID,
a.BANK_CODE,
a.BANK_NAME,
a.SUB_BANK_NAME,
a.BANK_ID,
a.CREATE_DT,
a.UPDATE_DT
) VALUES(
b.id,
b.bankCode,
b.bankName,
b.subBankName,
b.bankId,
sysdate,
sysdate
)
</update>