MySQL UPDATE with random number between 1-3

mysql random numbers between
karnival8 picture karnival8 · Feb 14, 2013 · Viewed 67.8k times · Source

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?

Answer

Rabbie picture Rabbie · Feb 14, 2013

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.

Related questions

MySQL select 10 random rows from 600K rows fast

How can I best write a query that selects 10 rows randomly from a total of 600k?

Read More
Generate GUID in MySQL for existing Data?

I've just imported a bunch of data to a MySQL table and I have a column "GUID" that I want to basically fill down all existing rows with new and unique random GUID's. How do I do this in MySQL ? …

Read More
Simple Random Samples from a Sql database

How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" answer is to: SELECT * FROM …

Read More