none
how to update table rows with random number values? RRS feed

  • 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

Answers

  • 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 Naomi NModerator Thursday, August 4, 2011 6:45 PM
    • Marked as answer by Rich P123 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
    Moderator
  • 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 Naomi NModerator Thursday, August 4, 2011 6:45 PM
    • Marked as answer by Rich P123 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

    P.S. If you have a question, do not add it to an old thread.  Many of the people who might respond to you will not see old threads.  Instead, start a new thread.  If you think your question is similar to a previously answered thread, it would be helpful to include a link to that old thread, but you want to ask your question in a new thread.

    Tuesday, January 13, 2015 8:02 AM