Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

806 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