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

[NSMan]

Grrr....Apparantly SQL doesn't like the idea of using aggregate's in an update statement. I'm using the following select statement to select a count of children records, and link them to parent records. Problem is, when I try to run an update off of this statement, SQL politly informs me that "An aggregate may not appear in the set list of an UPDATE statement...". My easiest solution is to simply select the data into a temptable, and link it from there. Is there another way to make this work?

select 
 count(child.id),
 parent.id
from 
 object parent,
 object child
where 
 parent.typ=2 and
 child.typ=4 and 
 parent.id=child.link1
group by
child.link1,
parent.id

 

[SQLBill]

What does your update statement look like?

-SQLBill

Posting advice: FAQ481-4875

 

[NSMan]

update parent 
set
 parent.a1=count(child.id)
from 
 object parent,
 object child
where 
 parent.typ=2 and
 child.typ=4 and 
 parent.id=child.link1
group by
child.link1,
parent.id,
parent.a1

 

[maswien]

I guess Update is a typo and it should be 'select', the correct sql should be:

select 
 count(child.id),
 parent.id
from 
 object parent,
 object child
where 
 parent.typ=2 and
 child.typ=4 and 
 parent.id=child.link1
group by
parent.id

 

[SQLDenis]

is parentID unique?

if it is then this should work, untested of course

update o set o.a1 =z.ChildCount
from
(select 
 count(child.id) as ChildCount,
 parent.id as parentID
from 
 object parent,
 object child
where 
 parent.typ=2 and
 child.typ=4 and 
 parent.id=child.link1
group by
child.link1,
parent.id) z join object o on z.parentID = o.parentID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Easily done with derived table.

But. why are you GROUPing by parent.id and parent.a1?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NSMan]

Oh yeah...derived tables...thanks!

About the grouping on a1, it was just an empty field, and I figured that might be what was hanging up the update statement...