SQL - Add Data to Existing Data From Another Table

Question

I have a temp table @table in my stored procedure that looks like this:

AgtID | Bonus
-------------
5063  | 0
1104  | 0

And a table bonus that looks like this:

AgtID | Contest | Points | Event
--------------------------------
5063  | 21      | 1000   | 1
5063  | 21      | 3000   | 3
1104  | 21      | 1000   | 1
1104  | 21      | 500    | 2

And an update query (currently) like thus:

UPDATE tt
SET Bonus = b.Points
FROM @table tt
INNER JOIN tblContestData_Bonus b
ON tt.AgtID = b.AgtID
where ContestID = 21;

Currently, when I run this query, it'll overwrite whatever data is in @table.Bonus with the data in bonus.Points. This is fine for a single record, but as we start getting more bonus point events, I need to have it add to my value.

I know some languages have a i += value... does SQL have something similar?

Answer 1

I figured it out right after posting. Funny how that works. What I did:

UPDATE tt
SET Bonus = coalesce(
        (select SUM(Points) as Points
        from bonus b
        where b.AgtID = tt.AgtID
        and ContestID = 21
        group by AgtID),0)
FROM @table tt;

Using the coalesce() to account for null values to math in following steps works fine even if there are no records.

Answer 2

I might be missing what you're after here, but you can add the existing value to the new value in an UPDATE:

UPDATE tt
SET Bonus = b.Points + Bonus
FROM @table tt
INNER JOIN tblContestData_Bonus b
ON tt.AgtID = b.AgtID
where ContestID = 21;

Answer 3

To do this properly in SQL, you need to pre-aggregate b. You cannot update a single record multiple times, so an update will does not accumulate when there are multiple matches. It only updates on (arbitrary) record.

Something like this will take all the bonus points and add them in:

UPDATE tt
    SET Bonus = coalesce(tt.Bonus, 0) + b.Points
FROM @table tt INNER JOIN
     (select agtId, sum(points) as points
      from tblContestData_Bonus b
      group by abtId
    ) b
    ON tt.AgtID = b.AgtID
where ContestID = 21;