Update one of 2 duplicates in an sql server database table

Question

I have got a table that got a column with duplicate values. I would like to update one of a 2 duplicate values so for example row1 = tom and row2 = tom.. I want to add a 1 or an a to one of them and that will be for many other duplicates in the same column. Basically just add one number or letter to every 1 of the duplicates so there's no more duplicates.

I got this query that will update all the duplicates but not one of them. Can anyone help?

UPDATE Table1    SET Column1 = 'a'  WHERE exists        (SELECT Column1 , COUNT(Column1 ) FROM Clients GROUP BY Column1  HAVING ( COUNT(Column1 ) > 1) ) 

Answer 1

Try This with CTE and PARTITION BY

;WITH cte AS (   SELECT       ROW_NUMBER() OVER(PARTITION BY Column1  ORDER BY Column1 ) AS rno,       Column1    FROM Clients )  UPDATE cte SET Column1 =Column1 +' 1 ' WHERE rno=2 

Answer 2

I think this simple update is what you're looking for;

UPDATE Table1 SET Column1=Column1+CAST(id AS VARCHAR) WHERE id NOT IN (   SELECT MIN(id)    FROM Table1   GROUP BY Column1 ); 

Input:

(1,'A'), (2,'B'), (3,'A'), (4,'C'), (5,'C'), (6,'A'); 

Output:

(1,'A'), (2,'B'), (3,'A3'), (4,'C'), (5,'C5'), (6,'A6'); 

An SQLfiddle to test with.