Solved

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

Posted on 2010-11-07
5
589 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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

713 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