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. stackoverflow.com/questions/8057565/…

sam yi
– sam yi

2013-12-26 22:09:57 +00:00
Commented Dec 26, 2013 at 22:09 — sam yi
– sam yi, Commented Dec 26, 2013 at 22:09

Sam · Accepted Answer · 2013-12-26 22:06:16Z

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, 2013 at 22:06

Sam

2,7613 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-12-26 22:06:06Z

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, 2013 at 22:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update sql table using inner join

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related