UPDATE a column in every row with a random unique number

Question

I have a small table of about 20k rows. In that table there is a column named random_uid (INT NOT NULL). I would like to update all 20k rows with a random unique number.

Since my table is small I dont think I need to use a string or a UUID so I went with

SELECT FLOOR(RAND() * 100000000) AS random_num
FROM table1 
WHERE "random_num" NOT IN (SELECT random_uid FROM table1)
LIMIT 1;

My problem is that I cant update and select from the same table, so I was having trouble creating the UPDATE query.

Edit: I do not have an issue with the above randomness, since I am not using this for any security purpoces, simply to create unique ids for each row that are not just incremented. Because of the select that am using to verify that the same number doesnt already exist in another row, I cant use UPDATE, thats the issue.

Answer 1

Conducted testing on 2M records, 100 iterations. The test is successful.

UPDATE IGNORE table1 SET random_uid = ( RAND( ) * ( SELECT countID
FROM (
SELECT MAX(random_uid) + COUNT(1) + 1 countID
FROM table1) AS t3)
) + ( 
SELECT maxID
FROM (SELECT MAX( random_uid ) maxID FROM table1) AS t)

Answer 2

Here's a simple way to do it. I filled a test table with 512 rows, then did this:

mysql> set @i = 0;

mysql> update table1 set random_num = @i:=@i+1 order by rand();

mysql> select * from table1 limit 10;
+----+------------+
| id | random_num |
+----+------------+
|  1 |        345 |
|  2 |        108 |
|  3 |         18 |
|  4 |        247 |
|  6 |        202 |
|  7 |        275 |
|  8 |        289 |
|  9 |        121 |
| 13 |        237 |
| 14 |        344 |
+----+------------+

The numbers are now randomly assigned to rows, but each row has a unique value.

It won't be as random while assigning values to subsequently inserted rows, though.