Pascal Claes Pascal Claes - 2 months ago 9
MySQL Question

sql update with inner join and where

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.

Answer

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.