T-SQL: How to make cell-values unique?

Question

If I have a table where the cells in a column should not have the same values, how do I check this and update? (I know I can set constraints in the settings, but I don't want to do that.)

Say the column name is called unique hash name and contains

Peter
Peter
Peter
Dave
Dave

and so on. I want that to transform to:

Peter
Peter1
Peter2
Dave
Dave1

What is the T-SQL for SQL Server to do that?

Update: For clarity's sake, let's call the table "Persons" and the cell I want unique "UniqueName". Could you make it a SELECT-statement, so I can test the result before updating. And I am using SQL Server 2005 and above.

Answer 1

This is same as http://stackoverflow.com/questions/319873/sql-how-to-append-ids-to-the-rows-with-duplicate-values#319928

Let me know if this works or your question is slightly different

Answer 2

EDIT: I've changed the query to use your field names and added a "select-only" query for you to preview.

This is actually pretty easy to do... just use ROW_NUMBER() with a PARTITION clause:

UPDATE Persons SET UniqueName = temp.DeDupded FROM
    (SELECT ID,
     CASE WHEN ROW_NUMBER() OVER
      (PARTITION BY UniqueName ORDER BY UniqueName) = 1 THEN UniqueName
     ELSE UniqueName + CONVERT(VARCHAR, ROW_NUMBER()
      OVER (PARTITION BY UniqueName ORDER BY UniqueName)-1) END AS DeDupded
    FROM Persons) temp
WHERE Persons.ID = temp.ID

If you want a "select-only", then here you go:

SELECT ID,
    CASE WHEN ROW_NUMBER() OVER
     (PARTITION BY UniqueName ORDER BY UniqueName) = 1 THEN UniqueName
    ELSE UniqueName + CONVERT(VARCHAR, ROW_NUMBER()
     OVER (PARTITION BY UniqueName ORDER BY UniqueName)-1) END AS DeDupded
FROM Persons

---

EDIT Again: If you're looking for a SQL Server 2000 Solution...

CREATE TABLE #Persons ( ID INT IDENTITY(1, 1), UniqueName VARCHAR(100) )

INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('Bob')
INSERT INTO #Persons VALUES ('John')
INSERT INTO #Persons VALUES ('John')

SELECT
    ID,
    CASE WHEN Position = 0 THEN UniqueName
     ELSE UniqueName + (CONVERT(VARCHAR, Position))
    END AS UniqueName
FROM
    (SELECT
     ID,
     UniqueName,
     (SELECT COUNT(*) FROM #Persons p2 WHERE
      p1.UniqueName = p2.UniqueName AND p1.ID > p2.ID) AS Position
    FROM
     #Persons p1) _temp

DROP TABLE #Persons

Answer 3

This feels like a pretty clear use-case for a trigger (insert,update).