How can I update this column with an aggregate function? [duplicate]

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
– Dale K, Commented Feb 11, 2021 at 0:26

Dale K · Accepted Answer · 2021-02-11 00:34:07Z

0

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

edited Feb 11, 2021 at 0:34

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 11, 2021 at 0:32

Greg Finley

615 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I update this column with an aggregate function? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related