mysql 对位更新，批量更新，mybatis批量更新

 1.知道多个ID 并且多个对象，转化成sql语句为

UPDATE table1    SET name = CASE id 
        WHEN 1 THEN 'new name1'
        WHEN 2 THEN 'new name2'
        WHEN 3 THEN 'new name3'
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

mybatis xml文件表达式为

<!-- 批量更新数据 -->
    <update id="updateBatch">
        update table1 set
        name =
        <foreach collection="list" item="item" index="index"
            separator=" " open="case id" close="end">
            when #{item.id} then
            #{item.name}
        </foreach>,
         title =
        <foreach collection="list" item="item" index="index"
            separator=" " open="case id" close="end">
            when #{item.id} then
            #{item.title }
        </foreach>

         where id in
        <foreach collection="list" item="item" index="index" 
            separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>

 当更新关系表的时候，并且过滤条件有两个，其中一个（roleId）是不变的

 

UPDATE role_right_rela,
 (
	SELECT
		9999 RIGHT_ID ,1 is_enable
	FROM
		DUAL
	UNION ALL
		SELECT
			99998 RIGHT_ID ,0 is_enable
		FROM
			DUAL
) tmp
SET role_right_rela.is_enable = tmp.is_enable
WHERE
	role_right_rela.ROLE_ID = 1
AND role_right_rela.right_id = tmp.RIGHT_ID

mybatis xml文件的书写 

<update id="updateRoleRightRealByRoleAndRightId">

		UPDATE role_right_rela,
		<foreach collection="rightDtos" close=")" open="(" separator=" UNION ALL " item="vo" >
			SELECT #{vo.id} RIGHT_ID,#{vo.status} is_enable FROM DUAL
		</foreach>
		tmp
		SET role_right_rela.is_enable = tmp.is_enable
		WHERE
		role_right_rela.ROLE_ID = #{roleId}
		AND role_right_rela.right_id = tmp.RIGHT_ID



	</update>