Set ParentKey correctly according the latest date-CodePudding

I've got a code which finds and set ParentKey. ParentKey gets its value from Key field with first condition based on common ProductID. But can't meet correct second condition: ParentKey should be setting according the latest (max) date from the Date column. How to improve existing code?

Initial table

 -------------------------------------------------
|ProductID  |   Date        |  Key   |  ParentKey 
 -------------------------------------------------
|111        |   2017-12-31  |   1    |   1
|111        |   2018-12-31  |   2    |   1
|111        |   2019-12-31  |   3    |   1
|111        |   2020-12-31  |   4    |   1
|222        |   2017-12-31  |   5    |   5
|222        |   2018-12-31  |   6    |   5
|222        |   2019-12-31  |   7    |   5
|222        |   2020-12-31  |   8    |   5
|333        |   2017-12-31  |   9    |   9
|333        |   2018-12-31  |   10   |   9
|333        |   2019-12-31  |   11   |   9
|333        |   2020-12-31  |   12   |   9

Desired Output

 -------------------------------------------------
|ProductID  |   Date        |  Key   |  ParentKey 
 -------------------------------------------------
|111        |   2017-12-31  |   1    |   4
|111        |   2018-12-31  |   2    |   4
|111        |   2019-12-31  |   3    |   4
|111        |   2020-12-31  |   4    |   4
|222        |   2017-12-31  |   5    |   8
|222        |   2018-12-31  |   6    |   8
|222        |   2019-12-31  |   7    |   8
|222        |   2020-12-31  |   8    |   8
|333        |   2017-12-31  |   9    |   12
|333        |   2018-12-31  |   10   |   12
|333        |   2019-12-31  |   11   |   12
|333        |   2020-12-31  |   12   |   12

Current code

  WITH NEW_ID
  AS
  (
  SELECT [Key],  ProductID, [Date],
    ROW_NUMBER() OVER (Partition BY u2.[Date], u2.[Key]
    ORDER BY [Date] ASC) RN -- actually [Date] ASC could be changed to [Date] DESC - the result won't change 

  FROM [MyTable] u2
  ) RN
        

update u        
set u.[ParentKey] = u3.[Key] 
from [MyTable] u
inner join  NEW_ID u3 ON u.ProductID = u3.ProductID and u3.RN=1

CodePudding user response：

To directly correct your code, I'd just add a partition by the ProductId...

WITH
  latest AS
(
  SELECT
    ProductID,
    [Date],
    [Key],
    ROW_NUMBER() OVER (
      PARTITION BY ProductID
          ORDER BY [Date] DESC
    )
      AS rn 
  FROM
    [MyTable]
)
UPDATE       
  MyTable
SET
  MyTable.[ParentKey] = latest.[Key] 
FROM
  MyTable
INNER JOIN
  latest
    ON MyTable.ProductID = latest.ProductID
WHERE
  latest.rn = 1

You should be able to simplify it even further though, using an updatable common table expression, and using FIRST_VALUE()...

WITH
  sorted AS
(
  SELECT
    ProductID,  -- Not strictly needed in the CTE, but it makes me feel more secure
    [Date],     -- Not strictly needed in the CTE, but it makes me feel more secure
    [Key],      -- Not strictly needed in the CTE, but it makes me feel more secure
    ParentKey,
    FIRST_VALUE([Key]) OVER (
      PARTITION BY ProductID
          ORDER BY [Date] DESC
    )
      AS latest_key
  FROM
    [MyTable]
)
UPDATE       
  sorted
SET
  [ParentKey] = latest_key

Demo: https://dbfiddle.uk/dcskKHRO