update statement problem

[jimfog]

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

[justsomeguy]

update busines_usersset phone='6973999099'where id IN (SELECT crID FROM credentials WHERE username='thelos55')

[jimfog]

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.

[justsomeguy]

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.

[jimfog]

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.