在更新語句中使用 SUM

我試圖SUM()在我的SET聲明中使用我的UPDATE. 當我嘗試這個時，我得到一個錯誤

消息 157，級別 15，狀態 1，第 234 行

聚合可能不會出現在 UPDATE 語句的集合列表中。

我應該更改哪些CTE內容才能執行此更新？

;WITH CTE AS 
(Select EC, CN, CID, EntryDate
       FROM OPENQUERY(192.55.108.214,'select 
                             count(es.estimatenumber) As EC
                             ,cm.an CN
                             ,cm.am CID
                             ,es.entrydate EntryDate
                             from etalac es 
                             INNER JOIN bacalac cm
                             ON es.CID = cm.am
                             GROUP BY cm.an, cm.am, es.entrydate'))
UPDATE d2016
SET TotalNumber = SUM(EC)
FROM CTE cte
INNER JOIN datafor16 d2016
ON cte.CID = ed2016.CID
WHERE CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016';

您可以在隨後的 cte 中進行聚合，並從那裡進行更新。

;WITH CTE AS 
(Select EC, CN, CID, EntryDate
       FROM OPENQUERY(192.55.108.214,'select 
                             count(es.estimatenumber) As EC
                             ,cm.an CN
                             ,cm.am CID
                             ,es.entrydate EntryDate
                             from etalac es 
                             INNER JOIN bacalac cm
                             ON es.CID = cm.am
                             GROUP BY cm.an, cm.am, es.entrydate'))
, uCTE as (
 select cid, sumEC=sum(EC)
   from cte
   where CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016'
   group by cid
)
UPDATE d2016
 SET TotalNumber = sumEC
FROM uCTE cte
 INNER JOIN datafor16 d2016
   ON cte.CID = d2016.CID

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