Pascal Claes Pascal Claes - 1 year ago 117
MySQL Question

sql update with inner join and where

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

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

Answer Source

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

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


  • 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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download