Pgsql批量更新数据
参照:https://www.cnblogs.com/ldj3/p/9288187.html
一般方式:性能较差
<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 =#{obj.goodsSn}
</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 #{obj.column1}
</foreach>
ELSE column1
END,
column2 =
CASE id
<foreach collection="list" item="obj">
WHEN #{obj.id} THEN #{obj.column2}
</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">
<!--@mbg.generated-->
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 #{item.origShipNameCn,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=VARCHAR}
</foreach>
</trim>
<trim prefix="MMSI = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.mmsi,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 #{item.regportCode,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=VARCHAR}
</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 #{item.mortgageFlag,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">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.hibernateFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="TRIAL_SHIP_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.trialShipFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="REMARK = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.remark,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_INSPECT_NO = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipInspectNo,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_CALLSIGN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipCallsign,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SAILINGAREA_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.sailingareaCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_HULL_MATERIAL_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipHullMaterialCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_BUILT_DATE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipBuiltDate,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="SHIPYARD_CN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipyardCn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIPYARD_EN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipyardEn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_BUILT_ADDR_CN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipBuiltAddrCn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_BUILT_ADDR_EN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipBuiltAddrEn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_LENGTH = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipLength,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_BREADTH = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipBreadth,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_DEPTH = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipDepth,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_GROSSTON = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipGrosston,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_NETTON = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipNetton,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_DWT = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipDwt,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_ENGINE_TYPE_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipEngineTypeCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_ENGINE_POWER = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipEnginePower,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_ENGINE_NUM = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipEngineNum,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_PROPELLER_NUM = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipPropellerNum,jdbcType=NUMERIC}
</foreach>
</trim>
<trim prefix="SHIP_PROPELLER_KIND_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipPropellerKindCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="ORIG_REGPORT_NAME = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.origRegportName,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_ID_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipIdFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="ORIG_SHIP_NAME_EN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.origShipNameEn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="ORIG_SHIP_REG_NO = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.origShipRegNo,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_REGION_TYPE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipRegionType,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_REBUILT_DATE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipRebuiltDate,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="SHIP_REBUILT_ADDR_CN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipRebuiltAddrCn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIP_REBUILT_ADDR_EN = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipRebuiltAddrEn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="ORIG_DELETION_DATE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.origDeletionDate,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="PERMANENT_SEAL_REMARK = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.permanentSealRemark,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="DETENTION_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.detentionFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SHIPID_SEAL_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.shipidSealFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="ORG_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.orgCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="CREATOR_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.creatorCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="CREATE_TIME = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.createTime,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="OPERATOR_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.operatorCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="OPERATE_TIME = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.operateTime,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="OPERATE_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.operateFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="SOURCE_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.sourceCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="DATA_ORG_CODE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.dataOrgCode,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="CREATE_DATE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.createDate,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="CREATOR = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.creator,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="LAST_UPDATE_DATE = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.lastUpdateDate,jdbcType=DATE}
</foreach>
</trim>
<trim prefix="UPDATE_BY = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.updateBy,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="DELETE_FLAG = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.deleteFlag,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="PROCESS_STATUS = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when SHIP_REG_NO = #{item.shipRegNo,jdbcType=VARCHAR} then #{item.processStatus,jdbcType=VARCHAR}
</foreach>
</trim>
</trim>
where SHIP_REG_NO in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.shipRegNo,jdbcType=VARCHAR}
</foreach>
</update>