Update column with duplicates to unique values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update column with duplicates to unique values

Author

Topic

Jaxon
Starting Member

5 Posts

Posted - 2012-05-15 : 00:01:30

I need to update a column that has duplicate values in it and rename them so they have a sequential number after it. My table has about 2000 rows of data. So this is what I currently have.

ID Type Size
Valve1 Valve 100
Valve2 Valve 150
Manhole Manhole 1050
Manhole Manhole 1050
Cesspit1 Cesspit 200
Cesspit2 Cesspit 200

and this is what I would like.

ID Type Size
Valve1 Valve 100
Valve2 Valve 150
Manhole1 Manhole 1050
Manhole2 Manhole 1050
Cesspit1 Cesspit 200
Cesspit2 Cesspit 200

I have about 1800 Manholes which I want to number from Manhole1 to Manhole1800. This is just a sample of the data.

I don't mind creating a new ID column if need be. I hope this is clear?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 00:05:58

have a look at ROW_NUMBER() function in t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-15 : 08:48:52

Also, your naming convention is terribad. Instead of an ID of Manhole1, it should just be a number. The TYPE column tells you what you have.
Another helpful link: http://en.wikipedia.org/wiki/Database_normalization

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-15 : 08:59:33

[code]DECLARE @Sample TABLE
(
ID VARCHAR(10),
[Type] VARCHAR(10),
Size INT
)

INSERT @Sample
VALUES ('Valve1', 'Valve', 100),
('Valve2', 'Valve', 150),
('Manhole', 'Manhole', 1050),
('Manhole', 'Manhole', 1050),
('Cesspit1', 'Cesspit', 200),
('Cesspit2', 'Cesspit', 200)

SELECT * FROM @Sample

-- SwePeso
;WITH cteTarget(ID, SeqID, Items)
AS (
SELECT ID,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Size) AS SeqID,
COUNT(*) OVER (PARTITION BY ID) AS Items
FROM @Sample
)
UPDATE cteTarget
SET ID = ID + CAST(SeqID AS VARCHAR(12))
WHERE Items > 1

SELECT * FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Jaxon
Starting Member

5 Posts

Posted - 2012-05-15 : 20:27:41

Awesome thanks so much SwePeso, worked like a charm.

@DonAtWork thanks for the advice, that was just a sample

@visakh16 thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 21:56:16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources