避免PRIMARY KEY约束冲突需用Partition还是Distinct？仅更新指定ClientID

现有表a_test1中，ProjectID=103的记录如下：

103 2
103 3

执行更新操作时触发主键冲突错误：

Violation of PRIMARY KEY constraint 'PK__GC_1234'. Cannot insert duplicate key in object 'dbo.a_test1'. The duplicate key value is (103, 1)

需求：仅更新上述两行中ClientID=3的记录，忽略ClientID=2的记录，避免主键冲突。

原T-SQL代码如下：

DROP TABLE IF EXISTS a_test1

CREATE TABLE a_test1 
(
    ProjectID int NOT NULL,
    ClientID int NOT NULL,

    CONSTRAINT [PK__GC_1234] 
        PRIMARY KEY CLUSTERED (ProjectID ASC, ClientID ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO a_test1 VALUES (101, 1) 
INSERT INTO a_test1 VALUES (101, 2) 
INSERT INTO a_test1 VALUES (103, 2) 
INSERT INTO a_test1 VALUES (103, 3) 
INSERT INTO a_test1 VALUES (104, 1)
INSERT INTO a_test1 VALUES (104, 2)
INSERT INTO a_test1 VALUES (104, 3)

DECLARE @newClientID int = 1

UPDATE a1
SET a1.ClientID = @newClientID 
FROM a_test1 a1
WHERE a1.ClientID IN ( 2, 3 ) 
  AND NOT EXISTS (SELECT 1
                  FROM a_test1 a2
                  WHERE a2.ClientID = @newClientID
                    AND a2.projectID = a1.projectID)

错误根源：原语句会把同一ProjectID下所有ClientID为2、3的记录都改为1，导致同一ProjectID下出现多条(ProjectID, 1)，触发联合主键冲突。

核心逻辑是每个ProjectID下仅选择一条符合条件的记录（即该分组内ClientID最大的那条）进行更新，以下两种方案均可实现：

方案1：使用ROW_NUMBER()窗口函数 #

通过ROW_NUMBER()按ProjectID分区、ClientID降序排序，筛选出每个分组内排序第一的记录（即该ProjectID下ClientID最大的条目），再结合原有的NOT EXISTS条件避免与已存在的(ProjectID,1)冲突：

DECLARE @newClientID int = 1

WITH TargetRecords AS (
    SELECT 
        ProjectID, 
        ClientID,
        -- 按ProjectID分组，ClientID降序排列，取每组第一条
        ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY ClientID DESC) AS RowNum
    FROM a_test1
    WHERE ClientID IN (2, 3)
)
UPDATE a1
SET a1.ClientID = @newClientID
FROM a_test1 a1
JOIN TargetRecords tr ON a1.ProjectID = tr.ProjectID AND a1.ClientID = tr.ClientID
WHERE tr.RowNum = 1
  AND NOT EXISTS (
        SELECT 1
        FROM a_test1 a2
        WHERE a2.ProjectID = a1.ProjectID AND a2.ClientID = @newClientID
    )

方案2：使用MAX()聚合函数筛选 #

通过分组聚合找到每个ProjectID下最大的ClientID，再匹配到具体记录进行更新：

DECLARE @newClientID int = 1

WITH MaxClientPerProject AS (
    SELECT ProjectID, MAX(ClientID) AS MaxClientID
    FROM a_test1
    WHERE ClientID IN (2, 3)
    GROUP BY ProjectID
)
UPDATE a1
SET a1.ClientID = @newClientID
FROM a_test1 a1
JOIN MaxClientPerProject mc ON a1.ProjectID = mc.ProjectID AND a1.ClientID = mc.MaxClientID
WHERE NOT EXISTS (
        SELECT 1
        FROM a_test1 a2
        WHERE a2.ProjectID = a1.ProjectID AND a2.ClientID = @newClientID
    )

效果说明 #

两种方案都会仅更新每个ProjectID下ClientID最大的那条记录（比如103对应的ClientID=3），不会同时修改ClientID=2的条目，因此不会生成重复的(ProjectID,1)主键值，彻底避免冲突。

内容的提问来源于stack exchange，提问作者Kung Fu Ninja