如何使用聚合函数更新该列?

Question

我为每一对GameId和TeamId计算了OREB的总和

使用这个查询，

查询图像。

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

使用这个查询的结果，我想更新这个表中的null列

表名为dimGames.

我怎么写一个update语句来完成这个?

Answer 1

改编这个答案，我想你想:

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