CODEWITHSUNDEEP
phpmysqlsqlrandomsql-update
SmileDeveloper
SmileDeveloper

Reputation: 366

mysql update random a column (fast)

How can you randomly update a table. So you give a column a random value. And the column (in example 'top') is unique. And if you choose between the numbers 10 to 20 and you have 10 rows that you can't have a number that isn't used. If you have

Test table
-Id-    -Top-
0        0
1        0
2        0
3        0

Change randomly the column 'top' (1 to 4)

Test table
-Id-    -Top-
0        4
1        1
2        3
3        2

If I use PHP it is very slow. But MySQL is fast. I don't use SELECT because I also need to save the answers. Can anyone help me? thanks a lot.

Mysql: 5.7.22

PHP: 7.2.7

I use Mysql PDO lib for PHP.

Upvotes: 0

Views: 195

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you just want to assign a sequential number arbitrarily to rows, you can use variables:

set @rn := 0;

update t
    set top = (@rn := @rn + 1)
    order by rand();

The assignment looks sequential, but the order by rand() means that the values are inserted on random rows. All the numbers are unique because they are sequential.

Upvotes: 1

Related Questions