MySQL UPDATE with random number between 1-3
MysqlRandomNumbersBetweenMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
Do this
UPDATE tableName SET columnName = FLOOR(RAND( ) + RAND( ));