How do I insert a random value into mysql?

Question

It looks like RAND is what I need but I'm having a bit of trouble understanding how it works.

I need to insert a random number between 60 and 120 into a couple thousand rows. Table name is: listing and the column name is: hits

Could you please help?

Answer 1

To make a random integer between 60 and 120, you need to do a bit of arithmetic with the results of RAND(), which produces only floating point values:

SELECT FLOOR(60 + RAND() * 61);

So what's going on here:

RAND() will produce a value like 0.847269199. We multiply that by 61, which gives us the value 51.83615194. We add 60, since that's your desired offset above zero (111.83615194). FLOOR() rounds the whole thing down to the nearest whole number. Finally, you have 111.

To do this over a few thousand existing rows:

UPDATE table SET randcolumn = FLOOR(60 + RAND() * 61) WHERE (<some condition if necessary>);

See the MySQL docs on RAND() for more examples.

Note I think I have the arithmetic right, but if you get values of 59 or 121 outside the expected range, change the +60 up or down accordingly.