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
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
ASKER
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'.
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You are very welcome.
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