Updating column in table a from top 1 in column b with foreign key relationship
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;