Having trouble in SQL where I need to update multiple records in a table based on the average of scores for each specific gameID

Question

This is an example of a question I got incorrect in the past and has no relevance other than learning the methodology to solve the problem.

I created a basic table with:

player id (int)

game id (int)

score (int)

difficulty varchar(10)

I flooded the first three columns with random numbers. What I want to is to assign difficulty for each specific game id based on the average of the score. Here is how I tried to assign the hard difficulty.

update score_board

set difficulty = 'hard'

where 3>= (select avg(score)

from score_board

group by gameid );

However, this fails as you cannot make a comparison between multiple rows obtained by the subquery. Is there a way to get the avg for each game id without actually specifying the game id in the query?

Answer 1

Close . . . need a correlation clause:

update score_board sb
    set difficulty = 'hard'
    where 3 >= (select avg(sb2.score)
                from score_board sb2 
                where sb2.gameid = sb.gameid
               );