Updating duplicating values - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating duplicating values

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2008-02-14 : 08:57:11

I have a table like that:

ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham

I want to update duplicated values at ColumnC like:

Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham

How can i do it?

Thanks in advance!

Note: Table is for creating email aliases from names...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 09:03:09

UPDATE f
SET f.ColumnC = f.ColumnC + CAST(f.RecID - 1 AS VARCHAR)
FROM (SELECT ColumnC, ROW_NUMBER() OVER (PARTITION BY COlumnA, COlumnB, ColumnC ORDER BY ColumnC) AS RecID
) AS f
WHERE f.RecID > 1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 09:04:18

Try

UPDATE t
SET t.ColumnC=t.ColumnC +CAST((t.RowNo -1) AS varchar(4))
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ColumnA,ColumnB ORDER BY ColumnA,ColumnB) AS RowNo,
ColumnA,
ColumnB,
ColumnC
FROM Table)t
WHERE t.RowNo <>1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 09:22:05

Both are very fond of ROW_NUMBER()

Madhivanan

Failing to plan is Planning to fail

LacOniC
Starting Member

29 Posts

Posted - 2008-02-14 : 09:23:16

I had a collation problem with these. Can't i do it by using only ColumnC?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 09:27:00

quote:
Originally posted by LacOniC

I had a collation problem with these. Can't i do it by using only ColumnC?

What is the error you are getting?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 09:28:16

[code]UPDATE f
SET f.ColumnC = f.ColumnC + CAST(f.RecID - 1 AS VARCHAR(12))
FROM (
SELECT ColumnC,
ROW_NUMBER() OVER (PARTITION BY ColumnC ORDER BY ColumnC) AS RecID
FROM Table1
) AS f
WHERE f.RecID > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

LacOniC
Starting Member

29 Posts

Posted - 2008-02-14 : 09:43:11

quote:
Originally posted by madhivanan

quote:
Originally posted by LacOniC

I had a collation problem with these. Can't i do it by using only ColumnC?

What is the error you are getting?

Madhivanan

Failing to plan is Planning to fail

ColumnA = AlI, ColumnB = ÖZTÜRK, ColumnC = ali.ozturk
ColumnA = AlI, ColumnB = ÖZTÜRK, ColumnC = ali.ozturk

ColumnA is different but ColumnC is same. So base column must be ColumnC. I didn't talk about it at startup, my mistake sorry.

LacOniC
Starting Member

29 Posts

Posted - 2008-02-14 : 09:49:00

Can't use that local letter here. Is's big "i". :P Above post seems wrong.

LacOniC
Starting Member

29 Posts

Posted - 2008-02-14 : 10:00:06

quote:

Originally posted by Peso

UPDATE	f
SET	f.ColumnC = f.ColumnC + CAST(f.RecID - 1 AS VARCHAR(12))
FROM	(
		SELECT	ColumnC,
			ROW_NUMBER() OVER (PARTITION BY ColumnC ORDER BY ColumnC) AS RecID
		FROM	Table1
	) AS f
WHERE	f.RecID > 1

E 12°55'05.25"
N 56°04'39.16"

This worked very well. Thank both of you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources