Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 35
grouping logic 6 49
Get Duration of last Status Update 4 32
Alter a column in sql 34 0
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

864 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now