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

问题描述

MySQL的新版本8.0现在支持 常见表格表达式 .

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屋！