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
    (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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.