mysql - sql update with inner join 和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%'

出于某种原因，我遇到语法错误:

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

不过还是想不通。如果我将 update 和 set 替换为 select，它就可以正常工作。

Answer 1

错误 1064 是 MySQL 语法错误。正确的 MySQL 语法是:

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

注意事项:

• JOIN 进入 UPDATE 子句。
• 表别名使查询更易于编写和阅读。
• 我猜 enabled 和 active 确实是数值。如果是这样，请不要使用单引号。