Update Table with Random numbers without duplicates in SQL Server

Problem Statement:

Recently, there was a question in one of SQL Server forum asking on updating all table rows with some Random numbers without duplicates. The person who had asked the question could achieve generating the random numbers in a way that there are some duplicates. However, his intention was updating the existing rows with unique random numbers.

Here is the script shared by Questioner


create table RandomNumberUpdate(Col1 bigint)
Go
Insert into RandomNumberUpdate Values(1)
Go 1000

Select * From RandomNumberUpdate

UPDATE RandomNumberUpdate
SET Col1 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)

Once after the update query executes, we can observe there are many duplicates in the table.You may try it yourself and see the duplicates. Use the below query to check the duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

Solution:

Here is a different approach to get UNIQUE values without duplicates.


UPDATE RandomNumberUpdate
SET Col1 = Abs(Checksum(NewId()))  

Query the table to confirm for any duplicates as below and see that there are no duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

--Clean the Code

Drop table RandomNumberUpdate
Advertisements

One thought on “Update Table with Random numbers without duplicates in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s