MYSQL查询出现语法错误

Question

我在 phpmyadmin 上运行此查询，它给出了语法错误，

Delete from users_roles as ur where ur.uid NOT IN (select u.uid from users as u)

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 'as ur where ur.uid NOT IN (select u.uid from users as u)' at line 1 

当我运行此查询时

select ur.uid from users_roles as ur where ur.uid NOT IN (select u.uid from users as u)

它给了我结果。

Answer 1

您不能使用 DELETE 形式的别名陈述。应该是:

DELETE FROM users_roles 
WHERE uid NOT IN (SELECT uid FROM users)

要使用别名，您必须使用不同的语法:

DELETE ur FROM users_roles AS ur
WHERE ur.uid NOT IN (SELECT uid FROM users)

此语法通常仅在执行 JOIN 时使用在 DELETE语句，这样就需要引用 JOIN 中的特定表和列。当您仅引用一张表时不需要它。例如，您可以将查询重写为:

DELETE ur
FROM users_roles AS ur
LEFT JOIN users AS u ON ur.uid = u.uid
WHERE u.uid IS NULL

查看 MySQL documentation 。 DELETE的第一种形式只允许tbl_name，第二种形式允许table_references，后者是可以定义别名的地方。