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.

I am a Dummy

Way back when I started this blog just under 4 years ago I wrote a post called 'An Oracle Developers Journey', in the early days the blog focused on the transition from Oracle to MySQL. The irony was that after a year or so professionally I move back the other way and for the last 3 years or so it's been all Oracle for me.

We all make mistakes, I know I have made some massive ones in the past, some were saved by a decent back up others were not so easy to recover from. It's been a while since I made anything like a big mistake (certainly not one that was noticed ;), once bitten twice shy tends to make you think about the consequences a little more.

But having now switched back to MySQL again I find myself really worried I'm going to make a mistake, in Oracle it's UPDATE/INSERT or more worryingly DELETE and then a COMMIT, but in MySQL (at least the way we are running it) there is no commit stage. I'm currently working on development systems but I just know that one day I'm going to delete or update something I really don't want to.

Thankfully MySQL is clever enough to make up for my itchy delete finger and includes the --safe-updates startup option. But I love the alias which is --i-am-a-dummy. I'm man enough to admit to being a dummy, well at least prone to doing something dum from time to time and that's why the first thing I did when I switched back to MySQL was to include --i-am-a-dummy in my startup alias.

So hands up who else is a dummy?

p.s. if you don't know --safe-updates sets the following session system variables.

SET sql_safe_updates=1,
sql_select_limit=1000,
sql_max_join_size=1000000;

sql_safe_updates is the key as it stops you running an update or delete without some sort of where clause.