on duplicate key update

<insert id="insertKycInfo">

INSERT INTO `okcoin_broker`.`user_kyc_info`(
`user_id`,
`kyc_info`,
`type`,
`country_id`,
`level`,
`status`,
`fail_reason`,
`remark`,
`create_time`,
`update_time`,
`exp_date`
) VALUES
<foreach collection="userKycInfo" item="element" index="index" open="(" separator="),(" close=")">

#{element.user_id},
<choose>
<when test="element.kyc_info ==null">null,</when>

<when test="element.kyc_info ==''">null,</when>

<otherwise>#{element.kyc_info},</otherwise>
</choose>
#{element.type},
#{element.country_id},
#{element.level},
#{element.status},
#{element.fail_reason},
#{element.remark},
date_format(STR_TO_DATE(#{element.create_time}, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s'),
date_format(STR_TO_DATE(#{element.update_time}, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s'),
<choose>
<when test="element.exp_date ==null">null</when>

<when test="element.exp_date ==''">null</when>

<otherwise>date_format(STR_TO_DATE(#{element.exp_date}, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')</otherwise>
</choose>
</foreach>
ON DUPLICATE KEY UPDATE
user_id = VALUES(user_id),
<choose>
<when test="element.kyc_info ==null">kyc_info = null,</when>

<when test="element.kyc_info ==''">kyc_info = null,</when>

<otherwise>kyc_info = VALUES(kyc_info),</otherwise>
</choose>
type = VALUES (type),
country_id = VALUES (country_id),
level = VALUES (level),
status = VALUES (status),
fail_reason = VALUES (fail_reason),
remark = VALUES (remark),
create_time = date_format(STR_TO_DATE(VALUES(create_time), '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s'),
update_time = date_format(STR_TO_DATE(VALUES(update_time), '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s'),
<choose>
<when test="element.exp_date ==null">exp_date = null</when>

<when test="element.exp_date ==''">exp_date = null</when>

<otherwise>exp_date = date_format(STR_TO_DATE(VALUES(exp_date), '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')</otherwise>
</choose>
</insert>

내저장소 바로가기 luxury515