使用cte在mysql中更新或删除

b1payxdu 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(315)

新版本的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

为了从表中删除重复项，就像在SQLServer中一样。
原来我错了。当我试图执行 DELETE 我从mysql workbench得到一个错误：
错误代码：1146。表“todelete”不存在
当我尝试执行 UPDATE 使用cte。
所以，我的问题是，一个人怎么能用 WITH 从句 UPDATE 或者 DELETE mysql中的语句（如8.0版手册所引用）？

mysql sql-update common-table-expression sql-delete mysql-8.0

来源：https://stackoverflow.com/questions/50799157/use-a-cte-to-update-or-delete-in-mysql

2条答案

按热度按时间

dxxyhpgq1#

因为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;

赞(0）回复(0）举报 2021-06-21

uqdfh47h2#

这似乎是mysql中发布的错误 8.x . 根据此错误报告：
在2015版sql标准中，不能在更新中定义cte；mysql允许这样做，但使cte成为只读的（我们现在更新文档来说明这一点）。这就是说，可以使用视图而不是cte；那么视图可能是可更新的，但是由于窗口函数的存在，它被具体化为一个临时表（它没有被合并），因此是不可更新的（我们也将在文档中提到它）。
以上所有内容也适用于删除。
如果您遵循上面的bug链接，您将看到一个建议使用cte的解决方法，但它涉及以一对一的Map方式将cte连接到原始目标表。根据您的示例，这是一个一揽子删除，不清楚您需要什么解决方法，如果继续使用cte进行删除。

赞(0）回复(0）举报 2021-06-21

我来回答

使用cte在mysql中更新或删除

2条答案

相关问题

热门标签

最新问答