How can I update this column with an aggregate function?

Question

I calculated a sum for OREB that is unique to every pair of GameId and TeamId

using this query,

select DGAM.GameID, HomeTeamID, SUM(OREB) as OREB_Home    
from dimGames as DGAM
join dimPerformance as DPERF on DGAM.GameID = DPERF.GAME_ID and DGAM.HomeTeamID = DPERF.TEAM_ID
group by DGAM.GameID, HomeTeamID

Using the results of this query I want to update the null column in this table

Table called dimGames.

How can I write an update statement to accomplish this?

Please don't use images for data, code or errors. Use formatted text. Just use a regular update statement and join your query on. — Dale K, Feb 11 '21 at 00:26

score 0 · Accepted Answer · edited Feb 11 '21 at 00:34

Adapting this answer, I think you want:

with agg as (
    select DGAM.GameID, HomeTeamID, SUM(OREB) as OREB_Home
    from dimGames as DGAM
    join dimPerformance as DPERF on DGAM.GameID = DPERF.GAME_ID and DGAM.HomeTeamID = DPERF.TEAM_ID
    group by DGAM.GameID, HomeTeamID
)
UPDATE dimGames dg
    SET dg.OREB_home = agg.OREB_Home
FROM agg
WHERE dg.GameId = agg.GameId and dg.HomeTeamId = agg.HomeTeamId

How can I update this column with an aggregate function?

1 Answers1