SQL help (updating using a count() in mySQL )

[minestrone]

First off I know I am rotten at SQL and have managed to busk it for most of my career.

I am trying to write a stored proc and want to update a column with the count of lines for that condition

update
A a , B b , C c
set
a.count = count(c.id)
where
a.id = b.a_id AND
b.id = c.b_id ;

I get a "Invalid use of group function" error

If I set the line to

a.count = 1

I inserts to the table OK. thanks for any help

[Zippy]

First off I know I am rotten at SQL and have managed to busk it for most of my career.

I am trying to write a stored proc and want to update a column with the count of lines for that condition

update
A a , B b , C c
set
a.count = count(c.id)
where
a.id = b.a_id AND
b.id = c.b_id ;

I get a "Invalid use of group function" error

If I set the line to

a.count = 1

I inserts to the table OK. thanks for any help

Try getting the count in a subquery?
update
A a , B b , C c
set
a.count = (SELECT count(c.id)
where
a.id = b.a_id AND
b.id = c.b_id )
where
a.id = b.a_id AND
b.id = c.b_id

I'm sure somebody will be along with a better idea soon

[lilelvis2000]

I think this could work - I'm not a mySQL expert.

update
A a , B b
set
a.count = (SELECT count(c.id)
where
b.id = c.b_id )
where
a.id = b.a_id

[wurzel]

or assign the value returned by count to a variable and use that instead?

[Zippy]

or assign the value returned by count to a variable and use that instead?

I think the value for the count will vary from row to row

[BrilloPad]

Personally I like temp tables. I know dbas hate them : but they make the code more readable.

<dons tin hat>

[minestrone]

i think it might be working....

2 secs

[minestrone]

Cheers guys

The subquery worked, just had to add in the tables with FROM in that though.

First time I have user mySQL stored procedures, usually I would have a update, set, from where but mySQL wants the from tables in the update bit

Had to go into the system and add some data under a diff user to test, you know you have been on this site too long when your test users are sockpuppet1, sockpuppet2 etc.

Table A is actually a temp table as well which gets built up with a few selects.

Cheers

[Zippy]

Cheers guys

The subquery worked, just had to add in the tables with FROM in that though.

Doh!