UPDATE tableName
SET
orderId1 = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
orderId2 = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
这句sql 的意思是,更新orderId 字段,如果id=1 则orderId 的值为3,如果id=2 则orderId 的值为4……
where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。
MyBatis 改写:
UPDATE tableName
<trim prefix="set" suffixOverrides=",">
<trim prefix="orderId1 = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.orderId1 != null">
when orderId1 = #{item.orderId1 } then #{item.orderValue1}
</if>
</foreach>
</trim>
<trim prefix="orderId2 = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.orderId2 != null">
when orderId2 = #{item.orderId2 } then #{item.orderValue2}
</if>
</foreach>
</trim>
</trim>
<WHERE>
<foreach collection="list" separator="or" item="item" index="index" >
id = #{item.id}
</foreach>
</WHERE>
示例:
update cust_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="is_social = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.isSocial != null">
when cust_cert_number = #{item.custCertNumber} then #{item.isSocial}
</if>
</foreach>
</trim>
<trim prefix="thirdpart_feedback_time = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.thirdpartFeedbackTime != null">
when cust_cert_number = #{item.custCertNumber} then #{item.thirdpartFeedbackTime}
</if>
</foreach>
</trim>
<trim prefix="is_thirdpart_feedback = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when cust_cert_number = #{item.custCertNumber} then 1
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="item" index="index" >
cust_cert_number = #{item.custCertNumber}
</foreach>