SQL 批量插入、批量更新

最新推荐文章于 2024-08-28 23:29:01 发布

muguazhi

最新推荐文章于 2024-08-28 23:29:01 发布

阅读量1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/muguazhi/article/details/97286524

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

oracle：

    <!-- 批量插入  主键自增-->
    <insert id="insertBatch" parameterType="java.util.List">
        insert into TAB_A (ID, CODE, NAME)
        select SEQ_TAB_A.Nextval, t.* from (
        <foreach collection="list" item="item" index="index" separator="union">
            (select
            #{item.code},#{item.name}
            from dual)
        </foreach>
        ) t
    </insert>

    <!-- 批量更新 -->
    <update id="updateBatch" parameterType="java.util.List">
        <foreach collection="list" index="index" item="item" open="begin" close=";end;" separator=";">
            update TAB_A
            set CODE = #{item.code,jdbcType=VARCHAR},
            NAME = #{item.name,jdbcType=VARCHAR}
            where ID = #{item.id}
        </foreach>
    </update>

mysql：

<!-- 批量插入 主键自增 -->
INSERT INTO TABLE_A (NAME, age) VALUES
< foreach collection = "list" item = "item" INDEX = "index" OPEN = "(" SEPARATOR = "),(" CLOSE = ")" > 
    #{item.name, jdbcType=VARCHAR},
    #{item.age, jdbcType=VARCHAR}
</ foreach >

<!-- 批量更新 方法1 -->
<foreach collection="list" index="index" item="item" open="" close="" separator=";">
      update table_a
      set age = age + 1,
      update_dt = UTC_TIMESTAMP(3),
      name = #{item.name,jdbcType=VARCHAR}
      where id = #{item.id,jdbcType=VARCHAR}
      and sex = #{item.sex,jdbcType=VARCHAR}
 </foreach>


<!-- 批量更新 方法2-->
<update id="updateBatch" parameterType="java.util.List">
   update sys_depart
   set parent_id = CASE id
   <foreach collection="list" item="item" index="index" open="" separator="" close="">
       WHEN #{item.id,jdbcType=VARCHAR} THEN #{item.parentId,jdbcType=VARCHAR}
   </foreach>
   END,
   depart_name = CASE id
   <foreach collection="list" item="item" index="index" open="" separator="" close="">
       WHEN #{item.id,jdbcType=VARCHAR} THEN #{item.departName,jdbcType=VARCHAR}
   </foreach>
   END
   WHERE id in
   <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
       #{item.id,jdbcType=VARCHAR}
   </foreach>
</update>