Renaming duplicates in a column

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

Renaming duplicates in a column

Author

Topic

Jaxon
Starting Member

5 Posts

Posted - 2012-05-13 : 17:36:57

Hi All, great site btw

I am trying to rename about 1200 assets out of 1800 assets. I want change the manhole to a unique ID. So manhole becomes manhole1, manhole2 and so on. The Asset_ID column is varchar. In the same column I have valves and other assets as well. I hope this is clear enough.

Asset_ID
manhole
manhole
manhole
manhole
valve1
Valve2

Asset_ID
manhole1
manhole2
manhole3
manhole4
valve1
valve2

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-13 : 17:49:51

Here is an example of how you can do this:

CREATE TABLE #Assets(Asset_ID VARCHAR(255));
INSERT INTO #Assets VALUES ('manhole'),('manhole'),('manhole'),('valve1'), ('valve2'),('pipe'),('pipe');

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY Asset_id ORDER BY (SELECT NULL)) AS RN,
	COUNT(*) OVER (PARTITION BY Asset_id) AS DupCount
	FROM #Assets
)
--SELECT * FROM cte;
UPDATE cte SET Asset_ID = Asset_ID + RIGHT('0000'+CAST(RN AS VARCHAR(32)),4)
WHERE DupCount > 1;

SELECT * FROM #Assets

DROP TABLE #Assets

Some useful information is on this link: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

You would need to take care to make sure that there is not already a row with Asset_ID = manhole1 in addition to all the other manholes.

Jaxon
Starting Member

5 Posts

Posted - 2012-05-13 : 18:47:42

thanks for the quick reply, using the above means I would have to add into line 2 ('manhole') 1200 times?

Can I somehow on line 2 use

INSERT INTO #Assets VALUES FROM ws_point where Asset_ID = 'manhole'

where ws_point is my table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 19:38:14

you can . it should be


INSERT INTO #Assets 
SELECT yourcolumnname FROM ws_point where Asset_ID = 'manhole'

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

Jaxon
Starting Member

5 Posts

Posted - 2012-05-13 : 20:41:18

Excellent it works thanks gentlemen.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 20:55:37

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources