SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update with count only where count > 0

    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
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

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