SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE  T1 
(
   [Key]	VARCHAR(512),
   [Value]	INT
);

INSERT INTO  T1 ([Key], [Value]) VALUES
	('xyz', 123),
	('xyz', 456),
	('xyz', 890),
	('abc', 123),
	('abc', 135),
	('abc', 204),
	('lmn', 890),
	('hij', 890);

SELECT * FROM T1;


Key
Value


xyz
123

xyz
456

xyz
890

abc
123

abc
135

abc
204

lmn
890

hij
890


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;

SELECT * FROM #Temp;


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

abc
135
135
1
No Change

abc
204
204
1
No Change

xyz
456
456
1
No Change



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'

SELECT * FROM  T1
            
  



Key
Value


xyz
123

xyz
456

xyz
890

abc
123

abc
135

abc
204

lmn
123

hij
123

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
abc	135	135	1	No Change
abc	204	204	1	No Change
xyz	456	456	1	No Change