MySQL sub-query update
Posted: Tue Jul 18, 2006 1:51 pm
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
I think this query may be close, but I could be wrong:
Any ideas?
I'll try to represent the table here
Code: Select all
table users
- userID
- parentID
- username
- sponsorCode: Select all
UPDATE users SET parentID = (SELECT userID FROM users WHERE username=sponsor)