sql写法:
UPDATE user_table
SET user_name= CASE id
WHEN 1 THEN '张三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王二'
END
WHERE id IN (1,2,3)
更新多个字段:
UPDATE user_table
SET user_name= CASE id
WHEN 1 THEN '张三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王二'
END,
SET user_card= CASE id
WHEN 1 THEN '001'
WHEN 2 THEN '002'
WHEN 3 THEN '003'
END
WHERE id IN (1,2,3)
WHERE id IN (1,2,3)不写也可以执行,在这里可以提升sql性能,避免全表扫描
mybatis写法:
mapper.java:
void updateUser(@Param("list") List<User> userList);
xml:
<update id="updateUser" parameterType="java.util.List">
update user_table
<trim prefix="set" suffixOverrides=",">
<trim prefix="user_name=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.userName}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
更新多个字段
<update id="updateUser" parameterType="java.util.List">
update user_table
<trim prefix="set" suffixOverrides=",">
<trim prefix="user_name=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.userName}
</foreach>
</trim>
<trim prefix="user_card=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.userCard}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>