MySQL中关联表为现有表新增并计算配送天数列问题

需求与已完成操作 #

需求：在shipping表中新增DaysTakenForDelivery列，存储orders表的Order_Date与shipping表的Ship_Date的日期差。
已完成操作：成功新增列，执行语句：

ALTER TABLE shipping ADD DaysTakenForDelivery INT;

之前尝试的错误操作及原因 #

错误1：触发错误1093 #

执行语句：

UPDATE shipping SET DaysTakenForDelivery = (
    select datediff(b.ship_date, a.order_date) AS DaysTakenForDelivery 
    from orders a
    JOIN shipping b ON a.Order_ID = b.Order_ID
);

错误提示：不能在FROM子句中指定更新的目标表'shipping'
原因：子查询未关联当前要更新的shipping行，且MySQL限制在UPDATE的子查询中直接引用目标表作为JOIN对象，同时该子查询会返回所有匹配的日期差结果，无法赋值给单个列。

错误2：触发错误1242 #

执行语句：

UPDATE shipping b SET DaysTakenForDelivery = (
    select datediff(b.ship_date, a.order_date) AS DaysTakenForDelivery 
    from orders a  
    WHERE a.Order_ID = b.Order_ID
);

错误提示：子查询返回多行
原因：orders表中存在重复的Order_ID记录，导致子查询返回多个结果，无法赋值给单个列。

针对MySQL 8.0.31的正确解决方案 #

方法1：使用JOIN直接更新（推荐，效率最优） #

通过JOIN关联两张表，确保每行shipping匹配对应orders记录后计算日期差：

UPDATE shipping s
JOIN orders o ON s.Order_ID = o.Order_ID
SET s.DaysTakenForDelivery = DATEDIFF(s.ship_date, o.order_date);

注：若orders表存在重复Order_ID，会使用最后匹配到的order_date计算；若需指定规则（如最早/最晚日期），可结合GROUP BY处理后再JOIN。

方法2：子查询加LIMIT确保返回单行 #

若允许从重复的Order_ID记录中取某一条（如第一条），可添加LIMIT 1：

UPDATE shipping s
SET DaysTakenForDelivery = (
    SELECT DATEDIFF(s.ship_date, o.order_date)
    FROM orders o
    WHERE o.Order_ID = s.Order_ID
    LIMIT 1 -- 可添加ORDER BY指定取哪一条，如ORDER BY order_date DESC
);

方法3：处理重复Order_ID后更新 #

若需针对每个Order_ID取特定规则的order_date（如最早日期），先聚合orders表再关联更新：

UPDATE shipping s
JOIN (
    SELECT Order_ID, MIN(order_date) AS earliest_order_date
    FROM orders
    GROUP BY Order_ID
) o ON s.Order_ID = o.Order_ID
SET s.DaysTakenForDelivery = DATEDIFF(s.ship_date, o.earliest_order_date);

进阶：使用生成列自动计算（无需手动更新） #

若希望列值自动维护，可将DaysTakenForDelivery设置为存储型生成列，MySQL会自动计算并存储值：

-- 先删除已手动新增的列
ALTER TABLE shipping DROP COLUMN DaysTakenForDelivery;
-- 创建生成列（假设orders表中Order_ID唯一）
ALTER TABLE shipping ADD DaysTakenForDelivery INT 
GENERATED ALWAYS AS (DATEDIFF(ship_date, (SELECT order_date FROM orders o WHERE o.Order_ID = shipping.Order_ID))) 
STORED;
-- 若orders表存在重复Order_ID，需指定取数规则，如取最早日期
ALTER TABLE shipping ADD DaysTakenForDelivery INT 
GENERATED ALWAYS AS (DATEDIFF(ship_date, (SELECT MIN(order_date) FROM orders o WHERE o.Order_ID = shipping.Order_ID))) 
STORED;

内容的提问来源于stack exchange，提问作者RUBINA