Hey, I want to update popularity(int) field in every email_data row, and set it equal to some formula of how many votes it has, how old it is, etc.
This is what I have so far:
UPDATE email_data SET popularity =
(SELECT (SUM(up) - SUM(down)*2)
FROM rating WHERE email_data_id=email_data.id)
(SELECT -DATEDIFF(CURDATE(), email_data.timestamp))
But this only works on a few rows, because if the email_data was never "rated" it won't have a row in the 'rating' table and will be skipped, I'd like it to just have a value of 0 in that case. Can anyone help me out on how to set up this query?
Last edited by farzher; 02-02-12 at 13:35.
JOIN ( SELECT email_data_id
, SUM(up) AS sum_up
, SUM(down) AS sum_down
BY email_data_id ) AS r
ON r.email_data_id = email_data.id
SET email_data.popularity =
CASE WHEN r.email_data_id IS NULL
ELSE r.sum_up - r.sum_down*2
- DATEDIFF( CURDATE(), email_data.timestamp )