using a MySQL query to randomize names

sfullman

suppose I have a vanilla table like this:

users

firstname
lastname
email
phone
address
city
state
zip

now I have a second table called firstname with just one column called value

that table has a bunch of first names:
John
Susan
Mike
Alan
Miguel
Zach
Hank
etc.
etc.
.. plus a whole lot more, obviously I can generate this from multiple places

NOW, what I want to do is join the two in such a way as each row in users is joined with a random first name in the 2nd table - and from there I can update and randomize my data.

Any way this can be done?

Thank you, I've tried a couple approaches and couldn't get it 🙂

Sam

sfullman

answered my own question:

as long as firstnames has a ID field, and the field is contiguous numerically, this will work except for a bug which I'll explain:

create table if not exists firstnames (
id int(11) unsigned not null primary key auto_increment,
value char(30) not null );
insert into firstnames set value='Clyde';
insert into firstnames set value='Harry';
insert into firstnames set value='Ellen';
insert into firstnames set value='Alex';
insert into firstnames set value='Bryan';
insert into firstnames set value='Steve';
insert into firstnames set value='Frank';
insert into firstnames set value='Joe Ellen';

SELECT
a.FirstName, a.LastName, b.value AS NewName
FROM addr_contacts a LEFT JOIN firstnames b ON
b.ID=1 + ROUND(((SELECT COUNT() FROM firstnames) - 1) rand(),0);

--update--
UPDATE
addr_contacts a LEFT JOIN firstnames b ON
b.ID=1 + ROUND(((SELECT COUNT() FROM firstnames) - 1) rand(),0)
SET a.FirstName=IF(b.value IS NOT NULL, b.value, a.FirstName);

the problem is that the returned random name in the firstnames table sometimes comes back as NULL and I can't figure out why. Any one have the answer to that, OR a more concise way of doing this?

johanafm

For b.value to be null, the has to be no matching b.id for the on part of the join clause. The only way this can happen is if b is empty (no rows in table), assuming sequential ids in b, starting at 1 with no gaps.
If you start your id numbering from any other value, say 2, or if you at any time have a gap in that table, such that ids are 1, 2, 4, ..., then you risk getting a non-existent id which returns null for b.value.

Also, do you really want to use one single random name from b for every record in a?

sfullman

yeah the id's need to be sequential I agree. what I can't figure out is why null IS showing, which it is.

see the code below. Use the query and replace addr_contacts against a person table you have in your database, and you'll see what I mean. Any ideas why the value is null, since in theory there will always be a match?

create table if not exists firstnames (
id int(11) unsigned not null primary key auto_increment,
value char(30) not null );
insert into firstnames set value='Clyde';
insert into firstnames set value='Harry';
insert into firstnames set value='Ellen';
insert into firstnames set value='Alex';
insert into firstnames set value='Bryan';
insert into firstnames set value='Steve';
insert into firstnames set value='Frank';
insert into firstnames set value='Joe Ellen';

SELECT
a.FirstName, a.LastName, b.value AS NewName
FROM addr_contacts a LEFT JOIN firstnames b ON
b.ID=1 + ROUND(((SELECT COUNT(*) FROM firstnames) - 1) * rand(),0);

again, try it and replace addr_contacts with your naem table and you'll see I'am getting a NULL value..

johanafm

Hmm I must say I was surprised at the results! Unfortunately, this is beyond my working knowledge of SQL, so I can only speculate (and suggest a PHP workaround).

I don't know why this happens, and you might have found a bug. It could also be that this does not follow the SQL spec for joins, even though MySQL lets it pass with undefined results. Compare your query to something seemingly similar,

SELECT ...
FROM ...
LEFT JOIN (SELECT value FROM firstnames ORDER BY RAND() LIMIT 1)

This query will pretty much use a static (one row) subset of the firstnames table to be joined with every row in addr_contacts, just like any ordinary join does, whereas your approach needs to execute once per row in addr_contacts.
If you want to find out exactly why this is happening, try MySQL forums, or even better, if you have access to Orcale support, use it. It would be interesting to get an exaplanation of what happens here.

I'd also recommend trying any other DBMS you might have access to, just to find out if results differ.

But, to speedily resolve the issue, create two querires. One selects all needed rows from addr_contacts, the other selects every single row from firstnames. Loop over firstnames and create an array of rows indexed from 0 to (N - 1), where N is the number of rows in firstnames.

Then, as you fetch each row from the addr_contacts result set, you create a random number between 0 and (N - 1) and add the value from that row index in your previously created array to the currently fetched row from the addr_contacts result set.