MySql case when实现批量更新

Java之葵花宝典

于 2024-06-23 08:32:12 发布

阅读量510

点赞数 3

分类专栏： mysql 文章标签： mysql windows 数据库

本文链接：https://blog.csdn.net/baidu_34848066/article/details/139892643

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

批量更新

1.sql：

#批量更新
UPDATE yd_org_purchase_region
    SET
    village_name = CASE id
        WHEN 202 THEN "大兴村"
        WHEN 203 THEN "大兴村"
        WHEN 204 THEN "大兴村"
    END,
		group_name = CASE id
        WHEN 202 THEN "第一组"
        WHEN 203 THEN "第二组"
        WHEN 204 THEN "第三组"
		END
		WHERE id in(202,203,204);

2.mybatis:

<update id="batchUpdateStoreHouse">
        UPDATE yd_organization
        SET org_name = CASE id
          <foreach collection="ydOrganizations" item="org">
              WHEN #{org.id} THEN #{org.orgName}
          </foreach>
              END,
        storehouse_volume = CASE id
        <foreach collection="ydOrganizations" item="org">
              WHEN #{org.id} THEN  #{org.storehouseVolume}
        </foreach>
              END,
          update_time=NOW()
          WHERE
	    id IN (
        <foreach collection="ydOrganizations" item="org" index="index" separator=",">
            #{org.id}
        </foreach>
        )
    </update>

3.java:

public void batchUpdateStoreHouse(List<YdOrganization> storeHouseList) {
        try {
            if (storeHouseList != null && storeHouseList.size() > 0) {
                List<YdOrganization> list = new ArrayList<>();
                for (YdOrganization ydOrganization : storeHouseList) {
                    list.add(ydOrganization);
                    if (list.size() % 30 == 0) {
                        ydOrganizationService.batchUpdateStoreHouse(list);
                        list.clear();
                    }
                }
                ydOrganizationService.batchUpdateStoreHouse(list);
            }
        } catch (Exception e) {
            log.error("", e);
        }
  }