Jump to content

update statement problem


jimfog
 Share

Recommended Posts

I am having difficulty implementing an update statement where it spans 2 tables. One table has 2 columns(among other, I am just mentioning the tables that relate to the problem), id, and phone(table name is business user) and the other also 2 columns(table name is credentials)- username and crID. The id of the business_user is a foreign key to crID of the credentials table. I want to update the phone in the business_user table. I have tried many many times with various queries to accomplished the aforementioned update but with no result till now. here is an example query where the username=thelos55;

update busines_usersset phone='6973999099'where credentials.username='thelos55'and busines_users.id=credentials.crID;

I get the following error using the above: Error code 1054, SQL state 42S22: Unknown column 'credentials.username' in 'where clause'Credentials.username is a correct column name, nonetheless MySQL says it is unknown. Can you suggest me any help here pleas?

Edited by jimfog
Link to comment
Share on other sites

My man...it works. After so much efforts...I will analyse the query now ans ask some questions later-for now I just wanted to inform you about the success of it.

Link to comment
Share on other sites

The reason for the original error is because you can only refer to columns in the table you're updating. The revised query uses a subquery, so that restriction doesn't apply inside the subquery.
Hm...I did not know this little detail.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...