I want to fill a column with a randomly chosen number for each record. In my case I want the number to be in the range 1-3.
Asked
Active
Viewed 8.3k times
53
-
5[What have you tried?](http://www.whathaveyoutried.com/) See [ask advice](http://stackoverflow.com/questions/ask-advice), please. – John Conde Feb 14 '13 at 00:06
-
http://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values – sgeddes Feb 14 '13 at 00:12
-
You haven't tried `UPDATE` and `RAND()`? – tadman Feb 14 '13 at 00:41
3 Answers
145
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.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
Rabbie
- 1,711
- 2
- 10
- 8
-
4Will there be a new random value for each row, or will it use the same for them all? – luckydonald Jul 23 '18 at 17:45
-
1
-
What if I wanted to update another column conditionally based on the random value? Is there a way using maybe `SET @myRandom = FLOOR(1+RAND()*3)` ? I mean in one query of course. – Sampgun Feb 20 '20 at 11:06
16
Use RAND() function. It returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i + 1)). For example, to obtain a random integer in the range the range 1<= R < 3, use the following statement:
UPDATE tableName
SET ColumnName= FLOOR(1 + rand() * 3);
N.B : RAND() produces random float values from 0 to 1.
