MySQL query not working, syntax error near INNER JOIN

Question

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 will 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
    ON
      users.user_uid = users_profiles.user_uid
    WHERE
      users.user_login = 'johndoe'
    AND
      users.user_uid = '11'

error i get when running sql query via phpmyadmin.

#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

Thanks.

score 1 · Answer 1 · answered Feb 07 '17 at 23:32

1

Try this:

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'

answered Feb 07 '17 at 23:32

McNets

10,352
3
32
61

many thanks works perfectly, i accepted @StephanLechner answer on the basis he posted it first but thank you never the less. – PHPLOVER Feb 07 '17 at 23:46

score 0 · Accepted Answer · edited May 20 '22 at 19:08

0

Try to exchange SET and INNER JOIN as shown, for example, in this SO answer:

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

edited May 20 '22 at 19:08

codewario

19,553
20
90
159

answered Feb 07 '17 at 23:32

Stephan Lechner

34,891
4
35
58

MySQL query not working, syntax error near INNER JOIN

2 Answers2