Generating "Random" Data using rand() and sub selects
I'm developing a demonstration site for new system we are about to unleash on the world, so far it's going great but one of the worst things about demo sites is the lack of real world data at hand. I don't want to give too much away but the site is vehicle sales based and I need to generate a bunch of data. The problem I needed to solve was that the table with the cars in didn't have associated dealer data and the pages looked very bare when there wasn't any as much of the data comes from the dealers table.
I didn't fancy going in to the table and amending 165 cars by hand, but I also didn't want to run the update in blocks as I wanted an a pretty even spread of the different cars between the 12 dealers. Take for example this data set...
mysql> select id, Make, dealer from cars_cars limit 5;
+----+------------+-----------+
| id | Make | dealer |
+----+------------+-----------+
| 1 | Land Rover | Aldershot |
| 2 | Land Rover | |
| 3 | Land Rover | |
| 4 | Land Rover | |
| 5 | Land Rover | |
+----+------------+-----------+
5 rows in set (0.00 sec)
Only one of the vehicles has a dealer attached. What I want is to populate that dealer column with a real dealer.
I always run updates in select mode first, to see what the final out put is like. First I want a way to get a random dealer, we can use rand() in the order by to generate a random number which MySQL will sort on.
mysql> select name from cars_dealers
order by rand() limit 1;
+------------+
| name |
+------------+
| Birmingham |
+------------+
1 row in set (0.00 sec)
mysql> select name from cars_dealers
order by rand() limit 1;
+--------+
| name |
+--------+
| Oldham |
+--------+
1 row in set (0.00 sec)
Now I can include that in my original select like so...
mysql> select id, Make,
(select name from cars_dealers
order by rand() limit 1) as dealer
from cars_cars limit 5;
+----+------------+-------------+
| id | Make | dealer |
+----+------------+-------------+
| 1 | Land Rover | Oxford |
| 2 | Land Rover | Aldershot |
| 3 | Land Rover | Coventry |
| 4 | Land Rover | Southampton |
| 5 | Land Rover | Birmingham |
+----+------------+-------------+
5 rows in set (0.00 sec)
Great, so I now have a random dealer against each vehicle, all I need now is to turn that into an update and we are good to go.
mysql> update cars_cars
set dealer =
(select name from cars_dealers
order by rand() limit 1)
where id > 0;
Query OK, 163 rows affected (0.01 sec)
Rows matched: 163 Changed: 163 Warnings: 0
I now have "random" data in my table. I put random in quotes because it is random within a set range of values not totally random as in totally unpredictable. Just to prove it really did work.
mysql> select id, Make, dealer from cars_cars limit 5;
+----+------------+---------------+
| id | Make | dealer |
+----+------------+---------------+
| 1 | Land Rover | Aldershot |
| 2 | Land Rover | Basingstoke |
| 3 | Land Rover | Wolverhampton |
| 4 | Land Rover | Aldershot |
| 5 | Land Rover | Wolverhampton |
+----+------------+---------------+
5 rows in set (0.00 sec)
* I used where id > 0 because as mentioned in my previous post I have safe update mode turned on.
Labels: mysql
posted by Andrew Gilfrin @ 2:45 PM
4 comments
