jimfog Posted February 6, 2013 Share Posted February 6, 2013 (edited) 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 February 6, 2013 by jimfog Link to comment Share on other sites More sharing options...
justsomeguy Posted February 6, 2013 Share Posted February 6, 2013 update busines_usersset phone='6973999099'where id IN (SELECT crID FROM credentials WHERE username='thelos55') Link to comment Share on other sites More sharing options...
jimfog Posted February 6, 2013 Author Share Posted February 6, 2013 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 More sharing options...
justsomeguy Posted February 6, 2013 Share Posted February 6, 2013 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. Link to comment Share on other sites More sharing options...
jimfog Posted February 6, 2013 Author Share Posted February 6, 2013 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now