mysql - 使用 CTE 在 MySQL 中更新或删除

Question

新版MySQL 8.0 支持Common Table Expressions .

根据手册:

A WITH clause is permitted at the beginning of SELECT, UPDATE, and DELETE statements:

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 中所做的那样.

事实证明我错了。当我尝试从 MySQL Workbench 执行 DELETE 语句时，出现错误:

Error Code: 1146. Table 'todelete' doesn't exist

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

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

Answer 1

这似乎是 MySQL 8.x 中已发布的错误。从这个bug report :

In the 2015 version of the SQL standard, a CTE cannot be defined in UPDATE; MySQL allows it but makes the CTE read-only (we're updating the documentation now to mention this). This said, one could use a view instead of the CTE; then the view may be updatable, but due to the presence of window functions it is materialized into a temporary table (it is not merged) so is not updatable (we're going to mention it in the doc as well).

All the above applies to DELETE too.

如果您点击上面的错误链接，您将看到建议使用 CTE 的解决方法，但它涉及将 CTE 加入一对一映射中的原始目标表。根据您的示例，这是一揽子删除，不清楚您需要什么解决方法，继续使用 CTE 进行删除。