CODEWITHSUNDEEP
sqlsql-server-2005
PhoenixUNI
PhoenixUNI

Reputation: 67

SQL - Add Data to Existing Data From Another Table

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?

Upvotes: 1

Views: 82

Answers (3)

PhoenixUNI
PhoenixUNI

Reputation: 67

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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;

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

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;

Upvotes: 0

Related Questions