Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Updating a field in every row?

    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?
    Last edited by farzher; 02-02-12 at 13:35. Reason: typos

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    I can't believe you even understood the question.

    Thanks, this works perfectly.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •