CODEWITHSUNDEEP
mysql
LargeTuna
LargeTuna

Reputation: 2824

How To Generate A Random Decimal Value In Mysql

I am trying to generate a random value between 0.01 - 0.50 to enter into mysql. I have 2.7 million rows that I need to execute this on.

Here is my script: 

 UPDATE FBAInventory SET buyBox = ROUND( 0.01 + RAND( ) * 8,2 );

It is generating values such as 4.20, 3.89 etc. I only want it to span from 0.01 - 0.50 and not to exceed this.

Does anyone know how to do this?

Thanks!

Upvotes: 5

Views: 7010

Answers (2)

Andy Jones
Andy Jones

Reputation: 6275

How about...

round(rand() * 0.49 + 0.01, 2);

Upvotes: 13

Elias
Elias

Reputation: 1427

You can use the floor function to generate a range of random numbers.

FLOOR(RAND() * (<max> - <min> + 1)) + <min>

where the max and min are inclusive. So in your case you would want

FLOOR(RAND() * 1.49 ) + 0.01

Upvotes: 1

Related Questions