MySQL delete with subquery给出语法错误

Question

在 MySQL 中，我有一个包含 2 个字段的表('ertek'):'id': int, 自增'mert':整数

如果我按 id ASC 对表排序，我想删除“mert”值低于前一行的行。

我的查询:

DELETE FROM ertek as t WHERE EXISTS (SELECT * FROM ertek AS t2 WHERE t2.id=t.id-1 AND t2.mert>t.mert)

我收到这个错误:1064 - 您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在 'as t WHERE 附近使用的正确语法EXISTS (SELECT * FROM ertek AS t2 WHERE t2.id=(t.id)-1 AND t2.mert>t' at line 1

请帮忙。

Answer 1

删除的表名不用别名试试

DELETE FROM ertek D
WHERE EXISTS 
(SELECT * FROM ertek AS t2 WHERE t2.id=D.id-1 AND t2.mert>D.mert)

或

  DELETE d1 FROM ertek d1 JOIN ertek d2 
  ON d2.id= (d1.id-1) AND d1.mert< d2.mert;