Update with select count

Databases
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

works great.

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

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

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

UPDATE t2
INNER
  JOIN ( SELECT count(f1) as cnt
              , f2
              , f3 
          from reviews 
         group 
            by f2
             , f3 ) AS t1
    ON t1.f2 = t2.f2
   AND t1.f3 = t2.f3
   SET t2.f1 = t1.cnt

:slight_smile:

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.