How to do the WHERE clause on a UPDATE statement that uses a JOIN?

mysql

Solution

You need to join the tables like this:

UPDATE `profiles`
JOIN `users` on profiles.user_id = users.user_id
SET `username` = 'foo',
`email` = 'bar@aol.com'
WHERE users.user_id = '1'

Problem

In this example the field username is inside the users table and the field email is in the profiles table. ``` UPDATE `profiles` JOIN `users` ". SET `username` = 'foo', `email` = 'bar@aol.com' WHERE users.user_id = '1' ``` In this where clause I am using users.user_id, but this causes all fields in the profile table to be updated with the email "bar@aol.com". What is the syntax to specify profiles.user_id in addition to the users.user_id in the WHERE clause?

Original source