Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Updating column with SUM from another table

    I have two tables.

    The first is USERS with id, name, unredeemed_points

    The second is POINT_ALLOCATIONS with id, user_id, activity, point_value.

    I need an update script that will update USERS.unredeemed_points with the total (SUM) of all the POINT_ALLOCATIONS.point_value for each USERS.id

    My current script fails when it does not find any matching records in POINT_ALLOCATIONS because USERS.unredeemed_points is flagged as Not Null.

    Any help appreciated.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Replace SUM(POINT_ALLOCATIONS.point_value) by
    COALESCE(SUM(POINT_ALLOCATIONS.point_value), 0)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Thanks Wim,

    I'm confused.
    I need the summed value to be placed in USERS.unredeemed_points. The script you provided does not mention this column.

    Am I missing something?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by markchimes View Post
    Am I missing something?
    yes

    wim showed you the change to make to your current script

    he did not mention USERS.unredeemed_points because he must have concluded that it wasn't necessary to write your whole script for you, since you said you already have one

    if you can't figure it out, the thing to do is to show your current script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    5
    Sorry Wim and R937,

    I obviously wasn't thinking.

    Here is my current script.
    UPDATE dbo.users
    SET unredeemed_points =
    (SELECT COALESCE (SUM(point_value), 0) AS Expr1
    FROM dbo.point_allocations AS F
    WHERE (dbo.users.id = user_id)
    GROUP BY user_id)

    This raises an error "... column unredeemed_points does not allow nulls..."

    Any help appreciated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE dbo.users
       SET unredeemed_points = totals.sum_points
      FROM dbo.users AS u
    LEFT OUTER
      JOIN ( SELECT user_id
                  , SUM(point_value) AS sum_points
               FROM dbo.point_allocations 
             GROUP 
                 BY user_id ) AS totals
    ON totals.user_id = u.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    5
    Thanks Riudi,

    Exactly the same error as before

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    (Stealing heavily from Rudy)
    Try this:
    Code:
    UPDATE dbo.users
       SET unredeemed_points = totals.sum_points
      FROM dbo.users AS u
    LEFT OUTER
      JOIN ( SELECT user_id
                  , SUM(point_value) AS sum_points
               FROM dbo.point_allocations 
               WHERE point_value IS NOT NULL
             GROUP 
                 BY user_id ) AS totals
    ON totals.user_id = u.id
    or this:
    Code:
    UPDATE dbo.users
       SET unredeemed_points = COALESCE(totals.sum_points, 0)
      FROM dbo.users AS u
    LEFT OUTER
      JOIN ( SELECT user_id
                  , SUM(point_value) AS sum_points
               FROM dbo.point_allocations 
             GROUP 
                 BY user_id ) AS totals
    ON totals.user_id = u.id
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by markchimes View Post
    Exactly the same error as before
    aw crap... i meant to write INNER JOIN, not LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There is a difference between using INNER JOIN compared to using LEFT OUTER JOIN.

    With INNER JOIN, only a part of all the records get a new value. Existing values will not be set to 0 when there are no more related records in point_allocations.

    With LEFT OUTER JOIN, all the records get a new value. Existing values will be set to 0 when there are no more related records in point_allocations.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Mar 2009
    Posts
    5
    Wim & Rudi,

    Thanks!
    That did the trick.

    cheers,
    Mark.

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
  •