sql update with inner join and where

Question

UPDATE newsreactions
SET newsreactions.enabled = '0'
FROM newsreactions
INNER JOIN users ON newsreactions.memberId = users.id
WHERE users.active =  '0' AND users.comment LIKE  '%spam%'

For some reason I'm getting a syntax error:

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 'FROM newsreactions INNER JOIN users ON newsreactions.memberId = users.id WHERE u' at line 3

Can't figure it out though. If I replace the update and set by a select it works fine.

Syntax for MySQL multi table UPDATE statement is available here: http://dev.mysql.com/doc/refman/5.6/en/update.html — spencer7593, Sep 16 '16 at 21:30

score 7 · Accepted Answer · answered Sep 16 '16 at 21:17

7

Error 1064 is a MySQL syntax error. The correct MySQL syntax is:

UPDATE newsreactions nr INNER JOIN
       users u
       ON nr.memberId = u.id
    SET nr.enabled = 0
WHERE u.active =  0 AND u.comment LIKE '%spam%';

Notes:

The JOIN goes in the UPDATE clause.
Table aliases makes the query easier to write and to read.
I am guessing that enabled and active are really numeric values. If so, do not use single quotes.

answered Sep 16 '16 at 21:17

Gordon Linoff

1,242,037
58
646
786

Thx! I used the answer to this question: http://stackoverflow.com/questions/9588423/sql-server-inner-join-when-updating How is my question different from it? – Sep 16 '16 at 21:22
1

@PascalClaes: That other question concerns Microsoft SQL Server, not MySQL. Those are different DBMS, and there are some significant differences in SQL syntax. The multi-table UPDATE syntax is an example of one of those differences. That's a big reason it's important to identify which database you are using (asking about). – spencer7593 Sep 16 '16 at 21:34

score 2 · Answer 2 · answered Sep 16 '16 at 21:18

The join clause should come before the set clause, and there should be no from clause in MySQL:

UPDATE newsreactions 
JOIN   users ON newsreactions.memberId = users.id
SET    newsreactions.enabled = '0'
WHERE  users.active =  '0' AND users.comment LIKE  '%spam%'

sql update with inner join and where

2 Answers2

Linked