Sql-server – Updating column in table a from top 1 in column b with foreign key relationship

sql serversql-server-2017

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.

Best Answer

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;

Related Solutions

Sql-server – Clarify – Need to add Multiple foreign key constraints

Instead of one column per position in TeamList, you can use lookup tables for Positions and Players. The TeamList table then defines which player is in which team and in what position for each round. You then don't need to enforce integrity on all players being from the same team because there is one row per player that defines the player:team relationship in TeamList (playerID and teamID).

I've included some DDL below as an example - you may need to add more data columns,etc. but it should be a good starting point.

Positions table:

 CREATE TABLE [dbo].[Positions]
    (
        [PositionID] [int] NOT NULL,
        [position_name] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [PositionID] ASC
    )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

The players table can be created as (not including team associations, but you could add this here):

CREATE TABLE [dbo].[Players](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [player_name] [varchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
    [playerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

You can then create your TeamList table like so

 CREATE TABLE [dbo].[TeamList]
    (
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [PositionID] int null,
    [PlayerID] int not null,
    CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED 
    (
    [TeamListID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Positions] FOREIGN KEY([Position])
    REFERENCES [dbo].[Positions] ([PositionID])
    GO

    ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Positions]
    GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[Players] ([playerID])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Players]
GO

-- Original FK definitions on round, team, coach, etc. go here

Sql-server – Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

You can do this pretty easily with OUTER APPLY (if you're on 2005 or newer). Note that there may be better performing ways of achieving the result, such as using ROW_NUMBER() - check execution plans if in doubt. Also, SELECT * is lazy and inadvisable; I'm just doing it here for illustrative purposes, and because I don't know the real structure of the Heartbeats table.

SELECT
    dv.Name,
    hb.*
FROM [Devices] as dv
    OUTER APPLY (
        SELECT TOP 1 *
        FROM Heartbeats
        WHERE Heartbeats.DeviceID = dv.ID
        ORDER BY DateEntered DESC
    ) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'

See Books Online for the finer points of OUTER APPLY vs. CROSS APPLY (it's much like OUTER JOIN vs. INNER JOIN). It was always such a pain doing queries like this in SQL Server 2000 where you didn't have OUTER/CROSS APPLY or the ROW_NUMBER() function.

Best Answer

Related Solutions

Sql-server – Clarify – Need to add Multiple foreign key constraints

Sql-server – Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

Related Question