help merge information

[Chris Elliott]

I am still pretty new to MySQL, so bare with me please...

I have two tables: one manages user logins for my site and another holds user information such as address, phone number, etc. The login table, called "dbusers" contains unique user id's. The contact information table, called "info" NEEDS these id's, but currently all the values are set to 0 for the id field. Unfortunately, the names (called "realname") in "dbusers" are in a different format than in "info" (called "name") but I think that the "like" operation should match them up without a problem (there are only about 50 entries). Here's what I have in mind, though the syntax doesn't exactly work:

UPDATE info SET id = (SELECT id FROM dbusers WHERE realname LIKE (SELECT name FROM info));

The idea is that every realname from dbusers should be checked against every name in users and, if they are alike, then the id in info should be set to its corresponding entry in dbusers. MySQL doesn't quite like my syntax, here's the error:

mysql> UPDATE info SET id = (SELECT id FROM dbusers WHERE realname LIKE (SELECT name FROM dbusers));
ERROR 1242 (21000): Subquery returns more than 1 row

I tried adding a "LIMIT 1" to the end seeing as how every match should be unique since there are no duplicate entries and then got a warning:

mysql> UPDATE info SET id = (SELECT id FROM dbusers WHERE realname LIKE (SELECT name FROM dbusers LIMIT 1));
Query OK, 0 rows affected, 15 warnings (0.03 sec)
Rows matched: 15 Changed: 0 Warnings: 15
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+


I bet I am missing some really easy way to do this, but I can't seem to find a better solution any where - or even one that makes this way work for that matter. Thanks for looking, I appreciate the help in advance!


-Chris Elliott
[e-mail]
[phone]



Edited 1 time(s). Last edit at 11/17/2008 10:45PM by Chris Elliott.