CODEWITHSUNDEEP
mysqlsql
mr.d
mr.d

Reputation: 386

Update sql table using inner join

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!

Upvotes: 0

Views: 183

Answers (2)

Sam
Sam

Reputation: 2761

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'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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';

Upvotes: 1

Related Questions