在更新語句中使用 SUM()

我以為我已經確定了這個語法，但是當我嘗試執行時，我收到了 Incorrect syntax near ) 的錯誤。為什麼我的語法不正確？

   ;with CTE As
   (
        Select max(entryID) As "MaxEntryID"
        ,userID FROM loghist group by userID
   )
   Select es.userID As userID, cm.companyName, SUM(COALESCE(eq.TotalValue,0)) As TotalValue
   FROM CTE vn
   Inner Join loghist es
   ON vn.userID=es.userID
   AND Coalesce(vn."MaxEntryID",0)=COALESCE(es.entryID,0)
   INNER JOIN company cm
   ON es.customerid = cm.customerid    
   Inner Join loghistpart ep
   ON ep.loghist = es.loghistid
   Inner Join lhq eq
   On eq.loghistpartid = ep.loghistpartid
   GROUP BY es.userID, cm.companyName
)
UPDATE fvi
Set TotalValue = loghistdTotalValue
FROM FullVarianceInfo fvi
INNER JOIN CTE mvn
ON fvi.userID = mvn.userID

嵌套 CTE 時，需要命名它們。

;with CTE As
(
    Select max(entryID) As "MaxEntryID"
    ,userID FROM loghist group by userID
), 
CTE2 AS (
Select es.userID As userID, cm.companyName, SUM(COALESCE(eq.TotalValue,0)) As TotalValue
FROM CTE vn
Inner Join loghist es
ON vn.userID=es.userID
AND Coalesce(vn."MaxEntryID",0)=COALESCE(es.entryID,0)
INNER JOIN company cm
ON es.customerid = cm.customerid    
Inner Join loghistpart ep
ON ep.loghist = es.loghistid
Inner Join lhq eq
On eq.loghistpartid = ep.loghistpartid
GROUP BY es.userID, cm.companyName
)

   UPDATE fvi
   Set TotalValue = loghistdTotalValue
   FROM FullVarianceInfo fvi
   INNER JOIN CTE2 mvn
   ON fvi.userID = mvn.userID

看起來您的UPDATE列也像寫的那樣倒退了。

Set TotalValue = loghistdTotalValue

應該是

Set loghistdTotalValue = TotalValue

引用自：https://dba.stackexchange.com/questions/177399