Menu
Got a big table and I want to add a column that has a randomly chosen number for each record. 1, 2, or 3.
Having a hard time. Any ideas?
452
Try this: UPDATE tableName SET columnName = FLOOR( 1 + RAND( ) *3 ); From the MySQL documentation for RAND : Returns a random floating-point value v in the range 0 <= v < 1.0.
Instead of rand() , use newid() , which is recalculated for each row in the result. The usual way is to use the modulo of the checksum.
select FLOOR( RAND() * (maximumValue-minimumValue) + minimumValue) as anyVariableName; Let us check with some maximum and minimum value. The maximum value we are considering is 200 and minimum is 100. The random number will be between 100 and 200 including 100 and 200 itself.
Generating random numbers is very simple in SQL Server. We can use the built-in function RAND() to generate random number.
Try this:
UPDATE tableName SET columnName = FLOOR( 1 + RAND( ) *3 ); From the MySQL documentation for RAND:
Returns a random floating-point value v in the range 0 <= v < 1.0.
So in the above query, the largest value which could be generated by 1 + RAND()*3 would be 3.999999, which when floored would give 3. The smallest value would occur when RAND() returns 0, in which case this would give 1.
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
