在UPDATE语句中使用GROUP BY时报错的技术咨询

首先，你这个UPDATE语句报错的核心原因是SQL Server不支持在UPDATE的FROM子句中直接使用GROUP BY并在SET中引用聚合函数——这种写法无法让数据库明确将聚合后的结果和要更新的LegMove表的每一行正确关联起来。另外还有个明显的拼写错误：JOIN条件里的l.Move_Create_Timestamp = m.FirstMoveTimewhere多写了where，应该是m.FirstMoveTime，这也会触发语法报错。

接下来给你两种可行的修正方案：

方案1：使用CTE（公共表表达式）先计算聚合结果 #

先通过CTE提前算出每个Leg_Key对应的目标字段值，再关联原表进行更新，这种写法可读性更高，逻辑也更清晰：

WITH DPKL_Aggregated AS (
    SELECT 
        m.Leg_Key,
        MAX(l.Status_Description) AS Event_Code,
        MAX(DATEADD(dd, DATEDIFF(dd, 0, l.Move_Create_Timestamp), 0)) AS Create_Date,
        MAX(l.Move_Create_Timestamp) AS PTimeStamp,
        MAX(DATEADD(dd, DATEDIFF(dd, 0, l.Move_Status_Timestamp), 0)) AS Acrual_Date,
        MAX(l.Move_Status_Timestamp) AS Actual_TimeStamp,
        MAX(l.Move_Reason_Code) AS Reason_Code,
        MAX(l.Move_Created_User) AS Create_User
    FROM wrkLegMove m
    INNER JOIN MovementMaster l WITH(NOLOCK) 
        ON l.Leg_Key = m.Leg_Key 
        AND l.Status_Description = m.Status_Description 
        AND l.Move_Create_Timestamp = m.FirstMoveTime  -- 修正拼写错误
    WHERE m.Status_Description = 'DPKL'
    GROUP BY m.Leg_Key, l.Shipment_Number, l.Shipment_Leg_Sequence
)
UPDATE lm
SET 
    lm.Event_Code = d.Event_Code,
    lm.Create_Date = d.Create_Date,
    lm.PTimeStamp = d.PTimeStamp,
    lm.Acrual_Date = d.Acrual_Date,
    lm.Actual_TimeStamp = d.Actual_TimeStamp,
    lm.Reason_Code = d.Reason_Code,
    lm.Create_User = d.Create_User
FROM LegMove lm
INNER JOIN DPKL_Aggregated d ON lm.Leg_Key = d.Leg_Key;

方案2：使用派生表替代CTE #

如果习惯用子查询，也可以用派生表的方式实现，逻辑和CTE完全一致：

UPDATE lm
SET 
    lm.Event_Code = d.Event_Code,
    lm.Create_Date = d.Create_Date,
    lm.PTimeStamp = d.PTimeStamp,
    lm.Acrual_Date = d.Acrual_Date,
    lm.Actual_TimeStamp = d.Actual_TimeStamp,
    lm.Reason_Code = d.Reason_Code,
    lm.Create_User = d.Create_User
FROM LegMove lm
INNER JOIN (
    SELECT 
        m.Leg_Key,
        MAX(l.Status_Description) AS Event_Code,
        MAX(DATEADD(dd, DATEDIFF(dd, 0, l.Move_Create_Timestamp), 0)) AS Create_Date,
        MAX(l.Move_Create_Timestamp) AS PTimeStamp,
        MAX(DATEADD(dd, DATEDIFF(dd, 0, l.Move_Status_Timestamp), 0)) AS Acrual_Date,
        MAX(l.Move_Status_Timestamp) AS Actual_TimeStamp,
        MAX(l.Move_Reason_Code) AS Reason_Code,
        MAX(l.Move_Created_User) AS Create_User
    FROM wrkLegMove m
    INNER JOIN MovementMaster l WITH(NOLOCK) 
        ON l.Leg_Key = m.Leg_Key 
        AND l.Status_Description = m.Status_Description 
        AND l.Move_Create_Timestamp = m.FirstMoveTime  -- 修正拼写错误
    WHERE m.Status_Description = 'DPKL'
    GROUP BY m.Leg_Key, l.Shipment_Number, l.Shipment_Leg_Sequence
) d ON lm.Leg_Key = d.Leg_Key;

额外优化提示 #

因为你的WHERE条件已经限定了m.Status_Description = 'DPKL'，并且JOIN条件里也绑定了l.Status_Description = m.Status_Description，所以原来SET里的CASE WHEN m.Status_Description = 'DPKL' THEN ... ELSE NULL END其实可以直接去掉——所有符合条件的记录都是DPKL状态，直接取对应字段再聚合即可，这样代码会更简洁。

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