不同数据库mybatis实现按照不同ID进行批量分行更新

想飞的蛋

已于 2024-05-30 10:14:34 修改

阅读量220

点赞数 3

文章标签：数据库 mybatis oracle postgresql mysql

于 2024-05-29 14:28:31 首次发布

本文链接：https://blog.csdn.net/CSDNBOZI/article/details/139295043

版权

PGSQL批量更新

<update id="updateTaskId" parameterType="map">
    UPDATE tablename
    SET task_id = tmp.task_id
    FROM (VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.tmp_id}, #{item.task_id})
        </foreach>
    ) AS tmp(tmp_id, task_id)
    WHERE tablename.id = tmp.tmp_id
</station>

MYSQL实现批量更新

<update id="updateWithValues" parameterType="list">
    UPDATE tablename
    INNER JOIN (
        SELECT tmp_id, task_id FROM (
            <foreach collection="list" item="pair" separator=" UNION ALL ">
                SELECT #{pair.tmp_id} AS tmp_id, #{pair.task_id} AS task_id
            </foreach>
        ) AS tmp_table
    ) AS tmp
    ON tablename.id = tmp.tmp_id
    SET tablename.task_id = tmp.task_id
</update>

ORACLE实现批量更新

<update id="updateTaskId" parameterType="list">
    MERGE INTO tablename
    USING (
        SELECT tmp_id, task_id
        FROM dual
        UNNEST (#{list, jdbcType=ARRAY, jdbcTypeName=YOUR_TYPENAME}) AS t (tmp_id, task_id)
    ) tmp
    ON (tablename.id = tmp.tmp_id)
    WHEN MATCHED THEN 
        UPDATE SET tablename.task_id = tmp.task_id
</update>