Update query using Case statements

Question

There is a table T1 with columns Key and Value. Combination of both key and value is primary key and combination is unique.

Key	Value
xyz	123
xyz	456
xyz	890
abc	123
abc	135
abc	204
lmn	890
hij	890

I wrote an update query to update all those Values 890 with 123. But while updating it breaks the uniqueness. There are many such values which needs to be replaced with some other values.

I tried an update query with case statements, but it is throwing errors:

UPDATE T1
SET Value = CASE 
                WHEN Value = 123 THEN 890
                WHEN Value = 450 THEN 222
                ELSE Value
            END;

Error:

Violation of primary key constraint. Cannot insert duplicate key in object. The duplicate key-value object is (xyz, 123)

Answer 1

--Find duplicates and rows to update
WITH a AS (
SELECT T1.[KEY], 
      t1.[Value], 
      CASE t1.[Value] 
         WHEN 890 THEN 123 
         WHEN 450 THEN 222
         ELSE [Value] 
      END as NewValue,
      CASE t1.[Value] 
         WHEN 890 THEN 'Update' 
         WHEN 450 THEN 'Update'
         ELSE 'No Change' 
      END as Action
FROM T1
) 
SELECT t1.[KEY], STRING_AGG(t1.[VALUE],',') as T1Value, a.NewValue, Count(*) as KeyValueCount,
  CASE 
  WHEN count(*) > 1 THEN 'Duplicate'  
  ELSE
    min(a.Action)
  END AS Action
INTO #temp    
FROM 
T1
INNER JOIN a ON a.[Value]=t1.[Value] AND a.[Key]=T1.[Key]
GROUP BY t1.[KEY], a.NewValue;

--Update the table T1
UPDATE T1
SET  T1.[Value]=#Temp.[NewValue]
FROM T1
INNER JOIN #Temp on #Temp.[Key]=T1.[Key] 
      AND #Temp.[T1Value]=T1.[Value]
WHERE
     #Temp.Action='Update'

fiddle

Identify duplicates and updates:

KEY	T1Value	NewValue	KeyValueCount	Action
abc	123	123	1	No Change
hij	890	123	1	Update
lmn	890	123	1	Update
xyz	890,123	123	2	Duplicate
vabc	135	135	1	No Change
abc	204	204	1	No Change
xyz	456	456	1	No Change

Updated table:

Key	Value
xyz	123
xyz	456
xyz	890
abc	123
vabc	135
abc	204
lmn	123
hij	123