Update sql table using inner join

Question

i'm trying to update my sql table using inner join. I have 2 tables: users and warnings

So i want to update my users table and set value 'yes' to filed users.awarn where users.id_level = '3' and inner join table warnings using id and check if warnings.active = 'yes'

bellow is my command:

UPDATE users
SET    users.awarn = 'yes'
INNER JOIN warnings
ON users.id = warnings.userid
WHERE users.id_level = '3'
AND warnings.active = 'yes'

but phpmyadmin return syntax error. Thanks in advance!

Duplicate. http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables — sam yi, Dec 26 '13 at 22:09

score 1 · Answer 1 · answered Dec 26 '13 at 22:06

1

This is the correct syntax for MySQL:

UPDATE users INNER JOIN
       warnings
       ON users.id = warnings.userid
    SET users.awarn = 'yes'
    WHERE users.id_level = '3' AND warnings.active = 'yes';

answered Dec 26 '13 at 22:06

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Dec 26 '13 at 22:06

1

Your order of operation is wrong SET has to come after JOIN so:

UPDATE users
INNER JOIN warnings
        ON users.id = warnings.userid
SET  users.awarn = 'yes'
WHERE users.id_level = '3'
AND warnings.active = 'yes'

answered Dec 26 '13 at 22:06

Sam

2,761
3
19
30

Update sql table using inner join

2 Answers2