casewhen+forEach实现多条件多值批量更新
case when + forEach 实现多条件多值批量更新
1、单个条件
<update id="updateBatch" parameterType="java.util.List">
update mydata_table
<trim prefix="set" suffixOverrides=",">
<trim prefix="status =case" suffix="end,">
批量更新sql语句
<foreach collection="list" item="item" index="index">
<if test="item.status !=null ">
when id=#{item.id} then #{item.status}
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
2、多个条件
<update id="updateBatch" parameterType="java.util.List">
update demo_table
<trim prefix="set" suffixOverrides=",">
status=
<foreach collection="list" item="item" open="case " close=" end,">
when field2=#{item.field2} and company_id=#{item.field3} then #{item.status}
</foreach>
create_time =
<foreach collection="list" item="item" open="case " close=" end,">
when field2=#{item.field2} and company_id=#{item.field3} then
<choose>
<when test="ateTime!=null">
#{ateTime}
</when>
<otherwise>now()</otherwise>
</choose>
</foreach>
</trim>
WHERE
<foreach collection="list" item="item" open="( " separator=") or (" close=" )">
device_num=#{item.field2} and company_id=#{item.field3}
</foreach>
</update>
注意：
foreach标签如果放在⼀条SQL外边的执⾏要⽐在⼀条SQL中写foreach然后根据条件循环更新的效率要低，数据量⼤的时候特别明显，建议foreach标签的使⽤写在⼀条SQL语句的中间

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。