Update with count only where count > 0

Databases
1

I want to update a field in one table with the count of records in another table but perform the update only on records where the count is > 0.

I tried


update users set numvotes = (SELECT count(reviewid)
FROM reviews
WHERE users.userid = reviews.userid
AND reviews.type = 'V' group by userid)

which tries to update records and set them null if there are no records.

2 
UPDATE users
INNER
  JOIN ( SELECT userid
              , COUNT(*) AS review_count
           FROM reviews
          WHERE type = 'V'
         GROUP
             BY userid ) AS c
    ON c.userid = users.userid
   AND c.review_count > 0
   SET users.numvotes = c.review_count

:slight_smile:

3

Awesome! I can honestly say I could have looked for that solution until the cows came home and never would have stumbled on it. But it works great. Thanks so much for the input. cheers