I wanted to fill a column in a table with random numbers so off the top of my head wrote the following sql :
update code set serialNumber = floor(rand() * 30000000000)
Unfortunately this set the serialNumber to exactly the same value since RAND() isn’t being re-executed for each row. I was in a bit of a hurry so instead found the following on google :
update code set serialNumber = abs(cast(cast(newid() as binary(4)) as int)) + 30000000000
Newid() is executed for each row and in this example it’s being used to created (aka ‘bodge’) an int. An interesting solution and did the trick for me.
You can get around the RAND() another way…
Create a VIEW with the SQL being SELECT RAND() AS [RandNumber]
Create a Scalar Function Calling the view to get a random number (different for each row) and return that number (Float by default, but you can convert and edit as needed)
The view is considered deterministic and the function will call the view one seperate call for each row… (You cannot use RAND() in the function since it is non-deterministic)
Problem Solved
Comment by Patrick — May 11, 2009 @ 6:39 pm
To postgres i use a sequence
create sequence teste
select mod(nextval(‘teste’),30)
not is exactly randon but solve my problem
Comment by battisti — July 23, 2009 @ 1:05 pm