# how to update table rows with random number values?

• ### Question

• I need to add some test data to a table that already contains x number of rows.  The column I need to update is a money column.  What I really want to do is to add random values like between 100 and 1000.  Is there a way to do this with an update query?  Or do I need a cursor?  Is there a random function?

Thanks

Rich P
Thursday, August 4, 2011 6:06 PM

• One way to get random numbers from 100.00 to 1000.00

Update YourTable Set YourColumn = (abs(cast(newid() as binary(6)) %90000) + 10000)/100.

Tom

• Proposed as answer by Thursday, August 4, 2011 6:45 PM
• Marked as answer by Thursday, August 4, 2011 6:52 PM
Thursday, August 4, 2011 6:14 PM

### All replies

• Hi, yes RAND(), try :

```select round(100+(abs(Rand()-0.1))*1000,0)
```

Best regards
Thursday, August 4, 2011 6:12 PM
• Hi Rich

There is a RAND() function in TSQL but this funciton will generate a random number between 1 and 0 (floating point number).....

The below script can generate a random number based on the range of yours

```Declare @maxRandomValue int
, @minRandomValue int ;

set @maxRandomValue = 1000
set @minRandomValue = 0

Select Cast(	(	(@maxRandomValue + 1) - @minRandomValue)
* Rand() + @minRandomValue As int) As 'randomNumber'
```

now coming to the update query of yours, the below one is untested but give a try...

```Declare @maxRandomValue int
, @minRandomValue int ;

set @maxRandomValue = 1000
set @minRandomValue = 0

update <table> set <your_column> = Cast(	(	(@maxRandomValue + 1) - @minRandomValue)
* Rand() + @minRandomValue As int) As 'randomNumber'
```

Nothing is Permanent... even Knowledge....
My Blog
Thursday, August 4, 2011 6:12 PM
• The following article is on the same topic:

http://www.sqlusa.com/bestpractices2005/roulettewheel/

Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
Thursday, August 4, 2011 6:13 PM
• One way to get random numbers from 100.00 to 1000.00

Update YourTable Set YourColumn = (abs(cast(newid() as binary(6)) %90000) + 10000)/100.

Tom

• Proposed as answer by Thursday, August 4, 2011 6:45 PM
• Marked as answer by Thursday, August 4, 2011 6:52 PM
Thursday, August 4, 2011 6:14 PM
• While there is a RAND() function that returns a random number between 0 and 1 that can be manipulated to any other range, it is not effective to update multiple rows with one update statement since it is executed only once per statement, not once per row. So, for example

Update <setle> set <column> = Rand()

will set the column value in every row to the same value.

Tom

Thursday, August 4, 2011 6:19 PM
• Thank you all for your replies.  This statement appears to yield random values for each row as I was looking for.

Update YourTable Set YourColumn = (abs(cast(newid() as binary(6)) %90000) + 10000)/100

It does randomize between 100 and 1000.  It turns out though, that I need to have some 0 values too.  How could I alter this statement to include 0 (zero) values?

Or how could I randomize my rowID column so that say 10% of the rows (230 rows) have a value of 0?

Rich P
Thursday, August 4, 2011 6:57 PM
• ```;with cte as
(select YourColumn, (abs(cast(newid() as binary(6)) %100000))/100. as RandomValue from YourTable)
Update cte Set YourColumn = case when RandomValue<100 then 0 else RandomValue end from cte;
```

Should set (approximately) 10% of the values to 0 and randomly distribute the other 90% between 100 and 1000.

Tom

Thursday, August 4, 2011 8:03 PM
• ```;with cte as

(select YourColumn, (abs(cast(newid() as binary(6)) %100000))/100. as RandomValue from YourTable)

Update cte Set YourColumn = case when RandomValue<100 then 0 else RandomValue end from cte;

```

Should set (approximately) 10% of the values to 0 and randomly distribute the other 90% between 100 and 1000.

Tom

Thanks for this tip.  I just tried it on a sql2008 DB.  How would I do this in a sql2000 DB?  Our main DB is on sql2000 (I have sql2008RS sp3 on my workstation only).  Plus, doing it in sql2000 will help me understand the underlying concept a little better as I don't see any reference to a Random (rand whatever they call it) function.

Thanks

Rich P
Friday, August 5, 2011 6:16 PM
• That method should work equally well in SQL 2000.  It depends on the NEWID() function which is intended to generate uniqueidentifiers.  It therefore generates a pseudo random value.  It works better than RAND() because NEWID() is called once for every row, but RAND() is called only once per statement.

Tom

Friday, August 5, 2011 8:31 PM
• That method should work equally well in SQL 2000.  It depends on the NEWID() function which is intended to generate uniqueidentifiers.  It therefore generates a pseudo random value.  It works better than RAND() because NEWID() is called once for every row, but RAND() is called only once per statement.

Tom

Thanks for this explanation.  I did note that Rand only provides one value for a dataset where your method (which uses NewID) provides a random value for each row as you state.   All part of the learning process.  Many thanks for the help and to all the repliers to my post.
Rich P
Friday, August 5, 2011 10:17 PM
• how to generate random numbers from 500 to 3000 or say whatever my range is?
Tuesday, January 13, 2015 6:42 AM
• Assuming you are looking for integer values >= 500 and <= 3000, then

```Declare @MinValueWanted int = 500,
@MaxValueWanted int = 3000;
Select abs(cast(newid() as binary(6)) % (1 + @MaxValueWanted - @MinValueWanted)) + @MinValueWanted;```

For other ranges, just change the values of the two variables.

To select a random value for each row in a table, do

```Declare @MinValueWanted int = 500,
@MaxValueWanted int = 3000;
Select <any columns you want from Mytable,
abs(cast(newid() as binary(6)) % (1 + @MaxValueWanted - @MinValueWanted)) + @MinValueWanted
From MyTable;```

Tom