How can I T-SQL update records with same PlayerName and PlayerCode but different season value with BornDateKey field corresponding to 2021-2022 season. The input and output I want to do is as follows.

Input:

Output (expected):

CodePudding user response：

Using an updatable CTE:

WITH cte AS (
    SELECT *, MAX(BornDateKey) OVER (PARTITION BY PlayerCode) AS MaxBornDateKey
    FROM yourTable
)

UPDATE cte
SET BornDateKey = MaxBornDateKey
WHERE BornDateKey IS NULL;

If you instead want to view your data this way, then just select from the above CTE and use MaxBornDateKey as the BornDateKey value.