Pgsql批量更新数据
Pgsql批量更新数据
⼀般⽅式：性能较差
<update id="updateData">
<foreach collection="list" item="obj" separator=";">
UPDATE jo_table
SET product_number = #{obj.subNum}
WHERE
club_id = #{obj.clubId}
AND product_sn =#{dsSn}
</foreach>
</update>
提⾼效率⽅式：
记住⼀定要加上 else 的情况，如果不加的话，当when条件不符合时，会导致你要修改的字段被置空，是很危险的。
<update id="handleAbnormal">
    UPDATE jo_table  SET
      product_number =
      CASE id
      <foreach collection="list" item="obj">
      　WHEN #{obj.id} THEN #{obj.number}
      </foreach>
    ELSE product_number
    END
    WHERE id IN
    <foreach collection="list" item="obj" separator=","open="(" close=")">
      #{obj.id}
    </foreach>
  </update>
多个字段进⾏更新
<update id="updateBatch">
UPDATE jo_table SET
column1 =
CASE id
<foreach collection="list" item="obj">
WHEN #{obj.id} THEN #{lumn1}
</foreach>
ELSE column1
END,
column2 =
CASE id
<foreach collection="list" item="obj">
WHEN #{obj.id} THEN #{lumn2}
</foreach>
ELSE column2
END
WHERE id IN
<foreach collection="list" item="obj" separator=","open="(" close=")">
#{obj.id}
</foreach>
</update>
拼接字段进⾏更新：
update jo_table  set number
CASE concat(字段1，字段2)
<foreach collection="list" item="obj" separator=";">
WHEN concat(#{obj.字段1},#{obj.字段2}) THEN #{obj.number}
</foreach>
ELSE number
END
软件⾃动⽣成的代码：
<update id="updateBatch" parameterType="java.util.List">
<!--@ated-->
update C_CB_SHIP_DATA
<trim prefix="set" suffixOverrides=",">
<trim prefix="SHIP_ID = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipId,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_NAME_CN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipNameCn,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_NAME_EN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipNameEn,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="ORIG_SHIP_NAME_CN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{igShipNameCn,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_FIRSTREG_NO = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipFirstregNo,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_IMO = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipImo,jdbcType=VA
RCHAR} </foreach>
</trim>
<trim prefix="MMSI = case" suffix="end,">
<foreach collection="list" index="index" item="item">
批量更新sql语句when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{si,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="REGPORT_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{portCode,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_ROUTE_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipRouteCode,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_TYPE_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipTypeCode,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_VALUE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipValue,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_SUMMER_DRAFT = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipSummerDraft,jdbcType=NUMERIC} </foreach>
</trim>
<trim prefix="SHIP_WIND_LEVEL = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipWindLevel,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="SHIP_MINIMUM_FREEBOARD = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipMinimumFreeboard,jdbcType=NUMERIC} </foreach>
</trim>
<trim prefix="SHIP_CONTAINER_NUM = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipContainerNum,jdbcType=NUMERIC} </foreach>
</trim>
<trim prefix="SHIP_PARKING_NUM = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipParkingNum,jdbcType=NUMERIC} </foreach>
</trim>
<trim prefix="SHIP_PASSENGER_NUM = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipPassengerNum,jdbcType=NUMERIC} </foreach>
</trim>
<trim prefix="IC_CARD_NO = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.icCardNo,jdbcType=V
ARCHAR} </foreach>
</trim>
<trim prefix="MORTGAGE_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{ageFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="BAREBOAT_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.bareboatFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="ALTER_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.alterFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="STATUS_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.statusFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="HANDOUT_CARD_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.handoutCardFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="FINANCIAL_LEASE_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.financialLeaseFlag,jdbcType=VARCHAR} </foreach>
</trim>
<trim prefix="HIBERNATE_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。