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
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
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
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.