代码之家 › 专栏 › 技术社区 › Giorgos Betsos

在mysql中使用cte进行更新或删除

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

5

Giorgos Betsos · 技术社区 · 6 年前

mysql的新版本8.0现在支持 Common Table Expressions .

根据手册:

在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 workbench得到的stament错误是:

错误代码:1146。表“todelite”不存在

当我尝试执行 UPDATE 使用CTE。

所以,我的问题是,一个人怎么能用 WITH 从句 更新 或 删除 MySQL中的语句(如8.0版手册中所引用的)?

2 回复 | 直到 6 年前

1

4

Tim Biegeleisen 6 年前

这似乎是MySQL中的一个已发布错误 8.x . 由此 bug report :

在2015年版的sql标准中,不能在update中定义cte;mysql允许cte,但将cte设置为只读(我们现在正在更新文档以说明这一点)。这就是说,可以使用视图而不是cte;然后视图可能是可更新的,但是由于存在窗口函数,它被具体化为一个临时表(它没有合并),因此不可更新(我们也将在doc中提到它)。

以上都适用于删除。

如果您遵循上面的bug链接,您将看到建议使用cte的解决方法,但它涉及到在一对一映射中将cte连接到原始目标表。根据您的示例(即全面删除),不清楚您需要什么样的解决方法来继续使用CTE进行删除。

2

Øystein Grøvlen 6 年前

由于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;