bostboy — 2010-10-26T11:17:35-04:00 — #1
I have a situation where I want to get a count of grouped records from one table and update a field with the count in another table.
the select statement works fine as
select count(t1.f1) as cnt, t1.f2, t1.f3 from reviews group by t1.f2, t1.f3
Now I want to take cnt and update a field t2.f1 where t1.f2 = t2.f2 and t1.f3 = t2.f3
Every syntax I've tried I doesn't work and I can't seem to find anything in the manual. Can someone point me in the right direction. Thanks
system — 2010-10-26T13:44:14-04:00 — #2
I'm not sure exactly what you want, but one way to do it could be to store cnt in a user defined variable and then use it to update the field you want to update
r937 — 2010-10-28T07:44:12-04:00 — #3
kalon, the problem is, there isn't just one count
you would need a separate user variable for every combination of t1.f2 and t1.f3 -- check the GROUP BY clause
JOIN ( SELECT count(f1) as cnt
, f3 ) AS t1
ON t1.f2 = t2.f2
AND t1.f3 = t2.f3
SET t2.f1 = t1.cnt
system — 2010-10-28T13:23:33-04:00 — #4
That's why I said I wasn't sure what the OP wanted because the op said
.......I want to get a count of grouped records......
which clearly means only 1 count.
For multiple counts it should have been
.......I want to get counts of grouped records......
but I'm not an English teacher so my understanding of English grammar could be wrong.