Violation of Composite Primary Key Constraint

Question

I am trying to run an update on a table that has composite primary keys.

Relevant code :

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 (104, 1)
INSERT INTO a_test1 VALUES (104, 2)
INSERT INTO a_test1 VALUES (104, 3)

DECLARE @newClientID int = 1


UPDATE a_test1
SET ClientID = @newClientID 
WHERE ClientID in ( 2, 3 ) 

There is already a record with ProjectID = 101 and ClientID = 1.

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

How do I add an IF .. EXISTS condition or a join to exclude these records?

Answer 1

Just check whether the UPDATE will create a PK which already exists and if so exclude it e.g.

DECLARE @NewClientID int = 1;

UPDATE at1
SET ClientID = @NewClientID 
FROM a_test1 at1
WHERE ClientID in (2, 3) 
AND NOT EXISTS (
    SELECT 1
    FROM a_test1 at2
    WHERE at2.ClientID = @NewClientID
    AND at2.ProjectID = at1.ProjectId
);