MySQL sub-query update

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ward
Forum Commoner
Posts: 74
Joined: Thu Jul 13, 2006 10:01 am

MySQL sub-query update

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What version of MySQL are you using? Subqueries like what you are doing are only available in later versions, I believe.
Ward
Forum Commoner
Posts: 74
Joined: Thu Jul 13, 2006 10:01 am

Post by Ward »

4.1.10
Ward
Forum Commoner
Posts: 74
Joined: Thu Jul 13, 2006 10:01 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

UPDATE users u1, users u2 SET u1.parentID = u2.userID where u1.sponsor = u2.username
Ward
Forum Commoner
Posts: 74
Joined: Thu Jul 13, 2006 10:01 am

Post by Ward »

Doh! :P
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Sweet.
Post Reply