Update multiple latest record on table using loop

Question

CREATE TABLE [dbo].[masterTable]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NOT NULL,
    [PID] [int] NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [State] [nchar](20) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [dbo].[masterTable]
           ([CID]
           ,[PID]
           ,[Description]
           ,[CreatedOn]
           )
     VALUES
           (189
           ,186
           ,'FC1_189'
           ,GETUTCDATE()),
           (189
           ,186
           ,'FC2_189'
           ,DATEADD(D, +1, GETUTCDATE())),
           (190
           ,186
           ,'FC1_190'
           ,DATEADD(d, +2, GETUTCDATE())),
           (190
           ,186
           ,'FC2_190'
           ,DATEADD(d, +3, GETUTCDATE())),
           (191
           ,186
           ,'FC1_191'
           ,DATEADD(d, +4, GETUTCDATE())),
           (191
           ,186
           ,'FC2_191'
           ,DATEADD(d, +5, GETUTCDATE()))

I have a table with 6 records I am trying to update the latest created record based on CID and PID data with the state 'Latest data' and other old created on should update state with 'old data' I tried this but for old created on data not working. check the below query.

Explanation: I currently have 6 records based on CID and PID data.

FC2_189 row is the latest record based on createdOn column so, for this row state column should update with 'latest data' and the other record FC1_189 are old records based on created on date, so compared to FC2_189 row this record is old data based on createdon column so state column should update with 'old data'.

same should happen with FC2_190,FC1_190 and FC2_191,FC1_191 data

SELECT Description,
    STATE,
    CreatedOn,
    PID,
    CID,
    ROW_NUMBER() OVER (
        PARTITION BY ContractID ORDER BY CreatedOn DESC
        ) contractRN
INTO #ControlTable
FROM masterTable

DECLARE @i INT = 1
DECLARE @count INT

SELECT @count = Count(*)
FROM #ControlTable


WHILE @i <= @count
BEGIN
    UPDATE masterTable SET State = 
    CASE WHEN CreatedOn = (SELECT MAX(CreatedOn) FROM masterTable)
    THEN 'Latest data'
    ELSE 'Old data'
    END
    SET @i = @i + 1
END

DROP TABLE #ControlTable

user_14868997 · Answer

You don't need a loop or joins at all. You can simply calculate a row-number inside a CTE, then update the CTE.

WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY PID, CID ORDER BY CreatedOn DESC)
    FROM masterTable
)
UPDATE cte
SET State = CASE WHEN rn = 1
                 THEN 'Latest data'
                 ELSE 'Old data'
            END;

Update multiple latest record on table using loop

1 Answers