Solved

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

Posted on 2010-11-07
5
592 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:briwagner
ID: 34080843
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
 

Author Comment

by:gogetsome
ID: 34080867
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
 
LVL 2

Accepted Solution

by:
briwagner earned 500 total points
ID: 34080923
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
 

Author Comment

by:gogetsome
ID: 34081064
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
 
LVL 2

Expert Comment

by:briwagner
ID: 34081143
You are very welcome.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question