Changing duplicate rows to a unique value

Question

I have a table (FORM) with a column like:

My aim is to change duplicate rows of 424 to 424+(a unique 6 digit number) but the query below ended up changing all occurrence of 424 to 424748793.

i need to make the query generate a unique value for each row with a duplicate of 424 excluding the first 424.

Thanks.

declare @count int

declare @num varchar (9)

declare @id varchar (9)

dcelare @generic varchar(9)

set @id = RIGHT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS varchar(6)), 6)

set @num= '424'

set @generic = @id+@num

select @count= COUNT(serialno) from FORM 

where  serialno = '424'

while @count <> 1

begin

update FORM SET  Serialno = @generic WHERE serialno = '424'

set @count = @count-1

 end

Are Matthew and Gina the same person? This seems remarkably similar to http://stackoverflow.com/questions/7285036/generate-unique-9-digits-values/ — Aaron Bertrand, Sep 03 '11 at 13:01

score 2 · Answer 1 · edited Jan 29 '14 at 12:30

If you have any other primary key column then you can update your data based on primary key value which is unique for all records. If you have no any other primary key column then you can do it by using temp table and row_number function like below :

SELECT Col1, Col2, Serialno,ROW_NUMBER() OVER(ORDER BY Serialno) AS RowNo INTO #TempTable FROM FormTable 

UPDATE #TempTable 
SET Serialno = Serialno + RIGHT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS varchar(6)), 6)
WHERE RowNo <> 1

DELETE FROM TestTable WHERE Serialno = '424'

INSERT INTO TestTable 
SELECT Col1,Col2 Serialno FROM #TempTable

DROP TABLE #TempTable

+1 But you don't need to use an intermediate `#temp` table for this. — Martin Smith, Sep 03 '11 at 09:41

Martin Smith · Answer 2 · 2011-09-03T09:20:47.327

To reproduce your current logic you would just need to do

UPDATE FORM 
SET  Serialno = '424' +
    RIGHT('000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999 AS VARCHAR(6)), 6) 
WHERE serialno = '424'

Of course this doesn't guarantee that all of the newly generated values will be unique or that they won't clash with any pre-existing values in the table.

If your table has no existing 424xxxxxx you can assign sequential serial numbers as follows.

;WITH CTE AS
(
SELECT *, 
       ROW_NUMBER() OVER (ORDER BY $/0) AS RN
FROM FORM
WHERE serialno = '424'
)
UPDATE CTE 
SET  Serialno = '424' + RIGHT('000000' + CAST(RN AS VARCHAR(6)), 6)

Unless there are > 999,999 dupes of 424. :-) – Aaron Bertrand Sep 03 '11 at 13:03 — Aaron Bertrand, Sep 03 '11 at 13:03

Changing duplicate rows to a unique value

2 Answers2