带有内部连接和位置的 sql 更新 [英] 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 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 3 行的FROM newsreactions INNER JOIN users ON newsreactions.memberId = users.id WHERE u"附近使用正确的语法

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

虽然想不通.如果我用 select 替换 update 和 set 它工作正常.

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

推荐答案

Error 1064 是 MySQL 语法错误.正确的 MySQL 语法是:

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

注意事项:

• JOIN 位于 UPDATE 子句中.
• 表别名使查询更易于编写和阅读.
• 我猜 enabled 和 active 是真正的数值.如果是这样，请不要使用单引号.

• 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.

这篇关于带有内部连接和位置的 sql 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！