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

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.

Answers


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

Need Your Help

What's the point of embedding Certificate Revocation List in PDF (CRL) rather than have pdf check online?

pdf

I'm not understanding something. There's a concept called embedding the CRL in a pdf so that if my private key is stolen, I could report it to the CA and they would update their CRL on the web sit...

How to calculate difference between two rows in a date interval?

sql ms-access-2010

I'm trying to compare data from an Access 2010 database based on a date interval. Example I have items from various purchase orders and I want to maintain the history of these item's delivery to a

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.