Sql-server – Use SUM In Update Statement

sql serversql-server-2008-r2t-sqlupdate

I am attempting to use SUM() in my SET statement for my UPDATE. When I try this, I get an error of

Msg 157, Level 15, State 1, Line 234
An aggregate may not appear in the set list of an UPDATE statement.

What should I alter in my CTE so that I can run this update? 

;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';

Best Answer

You can do the aggregate in a subsequent cte, and update from there.

;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
Related Question