0

Hello I have a table named scores and another named games. As basic as possible the tables look like this.

scores:

Player      AverageKills
extcy         5


games:

Player      Kills
extcy         5
extcy         6
extcy2       4
extcy         7

I am trying to take the Average of all the kills inside of the kills for just extcy and not extcy2 and add that to the data in scores by updating the original extcy row.

UPDATE scores, games
SET scores.AverageKills = AVG(games.Kills)
WHERE scores.Player = games.Player;

any ideas on what I am doing wrong with this code above?

moveit124
  • 61
  • 8

1 Answers1

1

Use a join and aggregation:

update scores s join
       (select player, avg(kills) avgkills
        from games g
        group by player
       ) g
       on s.player = g.player
    set s.averagekils = g.avgkills;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786