O
O
oleamm2011-03-26 10:17:52
MySQL
oleamm, 2011-03-26 10:17:52

Algorithm for changing a large number of rows in the database

There is a table in a DB, roughly speaking:
id | num

The id field is an integer, from 1 to 100,000. Some values ​​may be missing (for example: ..., 97, 98, 99, 100, 112, 113, ...).

Need to fill num with random integers.

Solution #1:
1. Get all values ​​from the table;
2. Make a loop, in each pass of which make a request update table set num=rand(0,999999) where id=$id

Is there a smarter way to avoid making 100,000 update requests?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
kirsan_vlz, 2011-03-26
@oleamm

mysql> select * from test;;
+----+------+
| id | num |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> update test set num = rand(12)*1000000;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from test;
+----+--------+
| id | num |
+----+--------+
| 1 | 157418 |
| 2 | 629671 |
| 3 | 676102 |
| 4 | 491496 |
| 5 | 429176 |
+----+--------+
5 rows in set (0.00 sec)

Comments, I think, are superfluous)
The only thing is that only the starting value of the randomizer is passed as a parameter to the rand() function, and not the range of the random value. And the function returns a value from 0 to 1. Therefore, by multiplying, bring the result to the desired order.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question