Inserting random numbers into a table in MYSQL

Question

Im trying to count the number of rows in the table and generate random numbers for the field 'random' Now this works:

SELECT COUNT(*) FROM my_table; 

and this works:

   UPDATE my_table SET random = FLOOR(6500 * RAND()) + 1;

But this doesn't work:

UPDATE my_table SET random = FLOOR((SELECT COUNT(*) ) * RAND()) + 1;

But this counts the rows as 0 and adds one so all fields have the number one instead of a unique random number.

Any ideas what I'm doing wrong would be most helpful.

Answer 1

You're asking what are you doing wrong. As the MySQL manual says "Currently, you cannot update a table and select from the same table in a subquery." What this means is that you can't do something like

update my_table set random = (select min(my_field) from r);

where you are doing a complete select as part of the update.

However, you can use a select without an error as you found, but the results are not what you expect - the scope of the SELECT statement as you used it is just the row being worked on at the time. You can test this by creating a field called num and doing this:

update my_table set random = (select count(*));

You'll see that random is set to 1 for every row, since the select is looking at just the one row you are updating at that moment.

The solution is to calculate the number of rows, store it in a variable, and reference that variable in another statement.

SET @row_count = count(*) from my_table;
UPDATE my_table SET random = FLOOR(@cnt * RAND()) + 1;

Answer 2

What about this?

SELECT @cnt := count(*) FROM my_table;
UPDATE my_table SET random = FLOOR(@cnt * RAND()) + 1;

Demo: http://sqlfiddle.com/#!2/a896d/4