在 MySQL 中使用 CTE 更新或删除 [英] Use a CTE to UPDATE or DELETE in MySQL

问题描述

新版 MySQL 8.0 现在支持 Common表表达式.

The new version of MySQL, 8.0, now supports Common Table Expressions.

根据手册:

允许在 SELECT、UPDATE 和 DELETE 语句的开头使用 WITH 子句:

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

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

所以，我想，给定下表:

So, I thought, given the following table:

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

in order to delete duplicates from the table, just like I could do in SQL Server.

事实证明我错了.当我尝试从 MySQL Workbench 执行 DELETE 语句时，我收到错误:

It turns out I was wrong. When I try to execute the DELETE stament from MySQL Workbench I get the error:

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

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

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

I also get an error message when I try to do an UPDATE using the CTE.

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

So, my question is, how could one use a WITH clause in the context of an UPDATE or DELETE statement in MySQL (as cited in the manual of version 8.0)?

推荐答案

这似乎是 MySQL 8.x 中已发布的错误.从这个错误报告:

This appears to be a published bug in MySQL 8.x. From this bug report:

2015版SQL标准中，不能在UPDATE中定义CTE；MySQL 允许它，但将 CTE 设为只读(我们现在正在更新文档以提及这一点).这就是说，可以使用视图而不是 CTE；那么视图可能是可更新的，但由于窗口函数的存在，它被具体化为一个临时表(它未合并)，因此不可更新(我们也会在文档中提到它).

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

以上所有内容也适用于 DELETE.

All the above applies to DELETE too.

如果您遵循上述错误链接，您将看到建议使用 CTE 的解决方法，但它涉及以一对一映射将 CTE 连接到原始目标表.根据您的示例，即一揽子删除，尚不清楚您需要什么解决方法来继续使用 CTE 进行删除.

If you follow the above bug link, you will see a workaround suggested for using a CTE, but it involved joining the CTE to the original target table in a one-to-one mapping. Based on your example, which is a blanket delete, it is not clear what workaround you need, were to proceed using a CTE for your delete.

这篇关于在 MySQL 中使用 CTE 更新或删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！