I've got two tables:
Table A: PlayerProfileId (PK), Email, HighScore, Name, UserName, Date
1 email@example.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.