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

Hello, I have a table with a comment, count and id columns. I have another table that is joined on the first table by the id column. I want to update the first table with the count of comments in the second table.
This statment is giving this error:  An aggregate may not appear in the set list of an UPDATE statement.

How do I get around this:

update t
set [Rcount] = (Count(p.PostItId))
from #PostIt t
inner join PostItComments p on t.postitid = p.postitid
gogetsomeAsked:
Who is Participating?
 
briwagnerConnect With a Mentor Commented:
My bad I forgot the set value. On my Mac at home so was not able to test. Here is the corrected code.


update t
set [Rcount] = p.pCnt
from #PostIt t
inner join
(select pCnt = Count(p.PostItId), Postitid
from PostItComments
group by Postitid) p
on t.postitid = p.postitid

Open in new window

0
 
briwagnerCommented:
Try This

update t
set [Rcount] =
from #PostIt t
inner join
(select pCnt = Count(p.PostItId), Postitid
from PostItComments
group by Postitid) p
on t.postitid = p.postitid
0
 
gogetsomeAuthor Commented:
Briwagner, Thanks for helping!

I stried your statement but it is giving me this error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'p'.
0
 
gogetsomeAuthor Commented:
Thank you so much for your help!

This does work

update t
set [Rcount] = p.pCnt
from #PostIt t
inner join
(select pCnt = Count(PostItId), Postitid
from PostItComments
group by Postitid) p
on t.postitid = p.postitid
0
 
briwagnerCommented:
You are very welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.