使用CTE在MySQL中更新或删除

Question

MySQL的新版本8.0现在支持常用表表达式。

根据手册：

在SELECT、UPDATE和DELETE语句的开头允许WITH子句：

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

所以，我想，考虑到下表：

ID lastName firstName
----------------------
1  Smith    Pat
2  Smith    Pat
3  Smith    Bob

我可以使用以下查询：

WITH ToDelete AS 
(
   SELECT ID,
          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn
   FROM mytable
)   
DELETE FROM ToDelete;

为了从表中删除副本，就像我在SQL Server中所做的那样。

结果我错了。当我试图从DELETE工作台执行MySQL语句时，我会得到以下错误：

错误代码: 1146。表'todelete‘不存在

当我尝试使用CTE执行UPDATE时，我也会收到一条错误消息。

所以，我的问题是，在UPDATE或DELETE语句的上下文中，如何在MySQL (如版本8.0的手册中所引用的)中使用DELETE子句？

Answer 1

这似乎是MySQL 8.x中发布的一个bug。从这个错误报告

在2015年版本的SQL标准中，CTE不能在UPDATE中定义；MySQL允许它，但使CTE只读(我们现在要更新文档以提及这一点)。这就是说，可以使用一个视图而不是CTE；然后视图可能是可更新的，但是由于窗口函数的存在，它被具体化为一个临时表(没有合并)，因此是不可更新的(我们也将在doc中提到它)。 以上所有内容也适用于删除。

如果您遵循上面的bug链接，您将看到建议使用CTE的解决方案，但它涉及到将CTE加入到原始目标表的一对一映射中。基于您的示例，这是一个毯子删除，它不清楚您需要什么解决办法，是继续使用CTE为您的删除。

Answer 2

由于CTE不可更新，因此需要引用原始表来删除行。我想你是在找这样的东西：

WITH ToDelete AS 
(
   SELECT ID,
          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn
   FROM mytable
)   
DELETE FROM mytable USING mytable JOIN ToDelete ON mytable.ID = ToDelete.ID
WHERE ToDelete.rn > 1;