bostboy — 2011-02-10T16:17:00-05:00 — #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.
update users set numvotes = (SELECT count(reviewid)
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.
r937 — 2011-02-10T16:21:11-05:00 — #2
JOIN ( SELECT userid
, COUNT(*) AS review_count
WHERE type = 'V'
BY userid ) AS c
ON c.userid = users.userid
AND c.review_count > 0
SET users.numvotes = c.review_count
bostboy — 2011-02-10T16:30:41-05:00 — #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