Page 1 of 1

MySQL sub-query update

Posted: Tue Jul 18, 2006 1:51 pm
by Ward
I'm trying to find a way to update my users table in one query. Here is the current situation: I have a users table, primary key is userID, foreign key is parentID (which refers to each user's parent user). I'm upgrading a system that used 'username' and 'sponsor' varchar fields as keys, and I need to update all parentID's. Basically, i need a query that will update each user's parentID to the userID of his 'sponsor'.

I'll try to represent the table here

Code: Select all

table users
- userID
- parentID
- username
- sponsor
I think this query may be close, but I could be wrong:

Code: Select all

UPDATE users SET parentID = (SELECT userID FROM users WHERE username=sponsor)
Any ideas?

Posted: Tue Jul 18, 2006 1:58 pm
by RobertGonzalez
What version of MySQL are you using? Subqueries like what you are doing are only available in later versions, I believe.

Posted: Tue Jul 18, 2006 2:00 pm
by Ward
4.1.10

Posted: Tue Jul 18, 2006 2:14 pm
by Ward
Well, i wound up writing a script to do it for me. It seems that its not possible to do a subquery update from the same table.

Posted: Tue Jul 18, 2006 2:17 pm
by Weirdan

Code: Select all

UPDATE users u1, users u2 SET u1.parentID = u2.userID where u1.sponsor = u2.username

Posted: Tue Jul 18, 2006 6:22 pm
by Ward
Doh! :P

Posted: Tue Jul 18, 2006 11:23 pm
by RobertGonzalez
Sweet.