CODEWITHSUNDEEP
sql-server-2008
Matthew
Matthew

Reputation: 33

Changing duplicate rows to a unique value

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

Serialno

424

536700045

345293885

424

466758884

424

424

244002678

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

Upvotes: 1

Views: 1623

Answers (2)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453328

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)

Upvotes: 1

Related Questions