sql - 使用 join 进行更新

Question

我正在尝试进行更新，这是我的查询

UPDATE t1
SET DOY = isnull(Sum(t2.Price),0)- isnull(Sum(t2.RestOfPrice),0) 
FROM customermaster t1 INNER JOIN History t2 
     ON t1.CustomerID = t2.CustomerID

查询抛出错误

An aggregate may not appear in the set list of an UPDATE statement.

Answer 1

您需要使用子查询或 CTE

使用子查询:

UPDATE t1
SET DOY =  K.Res
FROM customermaster t1
INNER JOIN 
(
    SELECT T2.CustomerID, 
           ISNULL(SUM(t2.Price), 0) - ISNULL(SUM(t2.RestOfPrice), 0) Res
    FROM History t2
    GROUP BY T2.CustomerID
) K ON t1.CustomerID = K.CustomerID;

使用 CTE:

WITH CTE AS
(
    SELECT T.CustomerID, 
           ISNULL(SUM(T.Price), 0) - ISNULL(SUM(T.RestOfPrice), 0) Res
    FROM History T
    GROUP BY T.CustomerID
)
UPDATE customermaster 
SET DOY = CTE.Res
WHERE CustomerID = CTE.CustomerID;