1

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';
  • You don't have a group by to use an aggregate but I think even with a proper aggregate it cannot but used in a set. – paparazzo Jan 31 '17 at 18:13
  • 1
    Really, rewrite that WHERE CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016'; to WHERE entrydate >= '20160101' AND entrydate < '20160123'; – ypercubeᵀᴹ Jan 31 '17 at 19:55
4

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
0

I think that is just

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.