如何更新SQL Server表中最新及历史记录的State字段

表结构与测试数据 #

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](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
    
INSERT INTO [dbo].[masterTable] ([CID], [PID], [Description], [CreatedOn], [state])
VALUES (190, 186, 'FC1', GETUTCDATE())
GO

INSERT INTO [dbo].[masterTable] ([CID], [PID], [Description], [CreatedOn], [state])
VALUES (190, 186, 'FC2', GETUTCDATE())
GO

INSERT INTO [dbo].[masterTable] ([CID], [PID], [Description], [CreatedOn], [state])
VALUES (190, 186, 'FC3', GETUTCDATE())
GO

需求描述 #

现有masterTable表中CID=190且PID=186的3条记录，其中FC3的CreatedOn字段值最新，需要：

• 将这条最新记录的State设为'Latest Data'
• 其余两条历史记录（FC1、FC2）的State设为'Old Data'

之前尝试的代码仅能更新最新记录，无法处理历史记录，以下是可行的解决方案：

用户原有尝试代码 #

WITH latestFC AS 
(
    SELECT TOP 1.*
    FROM [dbo].[masterTable]
    WHERE PID = 186
      AND CID = 190
    ORDER BY CreatedOn DESC
)
UPDATE latestFC
SET STATE = 'Latest Data'

解决方案 #

方案一：一次性批量更新（推荐） #

利用窗口函数给记录排名，通过CASE语句一次完成两种状态的更新：

WITH rankedRecords AS (
    SELECT 
        *,
        -- 按CID、PID分组，按CreatedOn倒序排名，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY CID, PID ORDER BY CreatedOn DESC) AS recordRank
    FROM [dbo].[masterTable]
    WHERE PID = 186 AND CID = 190
)
UPDATE rankedRecords
SET State = CASE 
               WHEN recordRank = 1 THEN 'Latest Data'
               ELSE 'Old Data'
           END;

方案二：分两步更新（逻辑直观） #

先统一将目标范围内的所有记录标记为历史状态，再单独更新最新的那条记录：

-- 第一步：把符合条件的所有记录先设为'Old Data'
UPDATE [dbo].[masterTable]
SET State = 'Old Data'
WHERE PID = 186 AND CID = 190;

-- 第二步：把最新的记录更新为'Latest Data'
WITH latestFC AS (
    SELECT TOP 1 *
    FROM [dbo].[masterTable]
    WHERE PID = 186 AND CID = 190
    ORDER BY CreatedOn DESC
)
UPDATE latestFC
SET State = 'Latest Data';

内容的提问来源于stack exchange，提问作者MD Akram Alam