MySQL Query not working

[genieuk]

Hi,

Not done any programming for a few years so am somewhat rusty again.

I have two tables users table and users_profiles table. I want to update users_profiles table on the condition that user_uid matches on both tables (users.user_uid and users_profiles.user_uid) and where users.user_login = 'johndoe' and users.user_uid = '11'.

It wil be executed in a php script, so johndoe would actually be users username (whatever is stored in session and same for users_uid. For simplicitity i added dummy data.

I run the query in phpmyadmin and get syntax error near INNER JOIN. I just cannot figure out what i'm doing wrong (probably wrote it entirely wrong) and have spent few hours trying to work it out without success.

Heres my sql query.

UPDATE 
    users_profiles
SET 
    users_profiles.user_fname = 'John', 
    users_profiles.user_lname = 'Doe', 
    users_profiles.user_gender = 'male'
INNER JOIN 
    users USING (user_uid)
ON
    users.user_uid = users_profiles.user_uid
WHERE
    users.user_login = 'johndoe'
AND
    users.user_uid = '11'

any help much appreciated.

Cheers.

UPDATE:

I am adding the error information i am getting when running query via phpmyadmin. I can only assume i am doing the sql query all wrong.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN
    users
ON
    users.user_uid = users_profiles.user_uid
WHERE
    user' at line 7

[rproffitt]

My first glance tells me you would use USING or ON but not both.
Read https://lornajane.net/posts/2012/sql-joins-with-on-or-using

[genieuk]

Thanks for the reply. I already tried with and without the USING (user_uid) and still get error. It seems to be something to do with the INNER JOIN by going from error below.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN
    users
ON
    users.user_uid = users_profiles.user_uid
WHERE
    user' at line 7 

Cheers

[rproffitt]

Your top edit. Now it looks confused. The error text doesn't match your SQL statement.

Even so, I'd practice the JOIN on a command line (without the update) until I found the right set.

[genieuk]

will play with it a bit more. the syntax error i get is confusing i admit, not sure why it displays it like that.

If i press simulate query instead of go in phpmyadmin i get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON users.user_uid, .user_uid FROM users_profiles WHERE users.user_login ' at line 7

[rproffitt]

And yet another error message. If you are trying different statements, share the statement and the error message for that statement.

Otherwise I can't follow. Maybe no one can.

I'd re-read the link I shared and sort out the JOIN.

[genieuk]

Solved.

For anyone having similar issues it needs to read like the below.

UPDATE 
    users_profiles
INNER JOIN 
    users
ON
    users.user_uid = users_profiles.user_uid
SET 
    users_profiles.user_fname = 'John', 
    users_profiles.user_lname = 'Doe', 
    users_profiles.user_gender = 'male'
WHERE
    users.user_login = 'johndoe'
AND
    users.user_uid = '11'

Cheers.