Updating a field in every row?

farzher · #1 (**permalink**) 02-02-12, 12:22

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:

Code:

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?

r937 · #2 (**permalink**) 02-02-12, 13:51

Code:

UPDATE email_data 
LEFT OUTER
  JOIN ( SELECT email_data_id
              , SUM(up) AS sum_up
              , SUM(down) AS sum_down
           FROM rating
         GROUP
             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
            THEN 0
            ELSE r.sum_up - r.sum_down*2
               - DATEDIFF( CURDATE(), email_data.timestamp )
        END

farzher · #3 (**permalink**) 02-02-12, 15:22

I can't believe you even understood the question.

Thanks, this works perfectly.

Internet Services

Web Development

Digital Marketing

Consumer Tech