无关联表数据更新：如何将tArticle表Weight值写入tPackage表对应字段

实现方案 #

不用临时表也能实现该需求，核心思路是给两张表的同订单记录按统一规则生成匹配序号，再按序号关联更新即可。如果使用的数据库不支持CTE和窗口函数（比如MySQL 5.x及更早版本），才需要用临时表辅助处理。

适用MySQL 8.0+、PostgreSQL、SQL Server的通用写法 #

用递归CTE展开商品表的count条记录，再通过窗口函数给包裹表和展开后的商品记录分别生成顺序号，关联后直接更新：

WITH 
-- 给同订单的包裹按ID升序生成顺序号
pkg_rn AS (
    SELECT 
        ID, 
        orderId,
        ROW_NUMBER() OVER (PARTITION BY orderId ORDER BY ID) AS rn
    FROM tPackage
),
-- 递归展开商品表，每条商品按count值生成对应数量的记录
article_expand AS (
    SELECT 
        ID AS article_id,
        Weight,
        orderId,
        count AS remain_cnt
    FROM tArticle
    UNION ALL
    SELECT 
        article_id,
        Weight,
        orderId,
        remain_cnt - 1
    FROM article_expand
    WHERE remain_cnt > 1
),
-- 给展开后的商品记录按顺序生成序号
article_rn AS (
    SELECT 
        Weight,
        orderId,
        ROW_NUMBER() OVER (PARTITION BY orderId ORDER BY article_id) AS rn
    FROM article_expand
)
-- 按订单和序号匹配，更新包裹重量
UPDATE tPackage p
INNER JOIN pkg_rn pr ON p.ID = pr.ID
INNER JOIN article_rn ar ON pr.orderId = ar.orderId AND pr.rn = ar.rn
SET p.Weight = ar.Weight;

低版本数据库临时表实现方案 #

如果数据库不支持CTE和窗口函数，可以用临时表存储编号后的记录完成更新：

1. 生成包裹表的顺序号临时表

CREATE TEMPORARY TABLE tmp_pkg_rn
SELECT 
    ID,
    orderId,
    @pkg_rn := IF(orderId = @cur_order, @pkg_rn + 1, 1) AS rn
FROM tPackage, (SELECT @pkg_rn := 0, @cur_order := 0) AS vars
ORDER BY orderId, ID;

2. 生成展开后的商品记录临时表（需要提前准备一张包含连续数字的辅助表nums，数字范围覆盖最大的count值即可）

CREATE TEMPORARY TABLE tmp_article_rn
SELECT 
    a.Weight,
    a.orderId,
    @art_rn := IF(a.orderId = @cur_art_order, @art_rn + 1, 1) AS rn
FROM tArticle a
INNER JOIN nums n ON n.n <= a.count,
(SELECT @art_rn := 0, @cur_art_order := 0) AS vars
ORDER BY a.orderId, a.ID, n.n;

3. 关联更新包裹重量

UPDATE tPackage p
INNER JOIN tmp_pkg_rn pr ON p.ID = pr.ID
INNER JOIN tmp_article_rn ar ON pr.orderId = ar.orderId AND pr.rn = ar.rn
SET p.Weight = ar.Weight;

注意事项 #

• 分配顺序可以根据业务需求调整，只要保证包裹表和商品表的排序规则一致即可，比如要按重量倒序分配就修改ORDER BY后的字段
• 建议更新前先把UPDATE语句替换为SELECT语句验证匹配结果，确认符合预期后再执行更新操作
• 你已经确认包裹总数量和商品总单位数完全匹配，不会出现匹配不到的情况，若数量不匹配可加WHERE条件过滤避免空值更新

内容的提问来源于stack exchange，提问作者J. S. Garcia