Updating column in table a from top 1 in column b with foreign key relationship I've got two tables:

Table A:
PlayerProfileId (PK),
Email,
HighScore,
Name,
UserName,
Date

1   g@s.com 37  Gustav  MegaBoi 2018-09-14 09:44:07.4505232


Table B:
ScoreId (PK),
Points,
Date,
PlayerId (FK)

3   36  2018-09-11 14:18:21.9372840 2


So every Player has a one to many relationship to their scores in table b.
I'm trying to write an update function that can update every row (players) highscore column.

Doing something like this works:

update PlayerProfile
set PlayerProfile.HighScore = a.Points
from 
    (select top 1 * from Scores where Scores.PlayerProfileId = 3 order by Points desc) as a
    where PlayerProfile.PlayerProfileId =  3


But I don't want to have to manually change the id number of the the players to update.

How can I change my update to automatically to this for all players?

What I was thinking is doing something some kind of join on table A and B and then only selecting the top 1 of each "group" and then update the highscore of that selected row. But I'm stumped as to how I can do this.

select *
from PlayerProfile
join Scores on PlayerProfile.PlayerProfileId = Scores.PlayerProfileId


If I could (for each playerId) only take the row with top Points and then use update the highscore through that row that would be great.
How can I change my update to automatically to this for all players?


Something like this, using a CTE?

with cte as(
select playerprofileid ,Points,row_number() over(partition by playerId order by points desc) as rownum
from scores)

UPDATE playerP
set HighScore = cte.Points
FROM Playerprofile PlayerP
inner join cte on cte.PlayerProfileId = PlayerP.PlayerProfileId 
where rownum = 1;