How do I update a column with different random numbers?

[MadProfessor]

Hi,

I have an empty column in a database, and I want to update it with a DIFFERENT random number for each record. When I use:

update table
set column = round(rand()*1000 + 1, 0);

it sets the same random number for all the records, which is no good to me. How do I get it to set a different random number for each record - do I need some sort of loop?

 

[rajeevnandanmishra]

Hi MadProfessor,
rand() function returns you the random values between 0 and 1. And if you take look at those values, you will find that they have a precision of almost 16/17 digits. So when you multiply that by 1000 and add 1 to it, it returns you the same value for all the rows. So, somehow you have to change the addition no. something like this:
declare @id int
select @id=0
update myTable
set myColumn=round(rand()*1000+@id,0), @id=@id+1



Hope this will move you in right direction.

 

[tlbroadbent]

rajeevnandanmishra's solution will provide a series of numbers rather than random numbers. Rand function will simply provide a different starting point for the sequence each time the query is run.

Update statements deal with sets of data so the value from the RAND function will only be generated once for the statement unless a seed is provided. Even with a seed there is no guarantee of uniqueness. The only way to guarantee unique values is to set a unique constraint on the column and update one record at a time with a random number.

What is the purpose for the random number in this table? Perhaps there is another way to accomplish your requirements. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MadProfessor]

Hi Terry,

I need the random number as I have a list of Unique ID numbers that are in sequence. These are to be attached to a querystring in a link in an email that is to be mailed out. The link in the email then links back to a site, where the UID pulls out the customer details. The random number was to add a small element of security to this - as otherwise you could just adjust the UID and see someone else's details. With a random number in the querystring that also has to be matched by the database, this is a lot harder to guess (it doesn't need to be totally foolproof as there's no critical info that will be pulled out of the databasem, and I figure this method adds a modicum of security).

The original expression I wrote above produces a random number that I think is sufficient for me, it's more a case of getting a different one in each record without having to do each record manually.

I wish there was some way I could loop through each record and generate a new random number for each record.

Thanks for your help.

 

[tlbroadbent]

You could write a loop but if the number isn't that critical you can use an update statement. As I mentioned RAND will produce a different number if a seed is provided.

Try this code.

Update table
Set Column = 1000000*rand(UID);

UID is used as the seed for the RAND function. It would be the unique identifier on your table. I assume it is an integer. I multiplied 1000000 to increase the chances of generating a unique random number. No need to round if column is type INT.
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.