如何在MySQL上运行更新查询？

Question

我正在尝试创建一个CTE，我认为这很好。但是，我想在这个CTE上运行一个更新查询，但是我一直在MySQL WorkBench中得到以下错误，

"Error Code: 1288. The target table pptest of the UPDATE is not updatable"

我已经四处看看，但没有不了解任何工作周围，而且最重要的是，我也不太熟悉我的MySQL。我的目标基本上是创建一个表/视图，其中包含一堆被分区的记录，并在每一行上都有一个索引号，这将由"row_num“表示。这是将表中的重复数据分组在一起，然后我希望在row_num大于1的结构上运行一个更新查询。所以逻辑很简单，但我无法找到实现目标的任何其他方法。有人能帮忙吗？

我之前运行的完整查询是，

WITH cte as (select *, row_number() over (partition by col_a order by col_a) row_num from db_name.table_name) 
update cte set col_b='test' where row_num > 1

Answer 1

在MySQL中不支持这种语法。实际上这看起来像SQL Server语法..。您不能只是将查询从一个数据库移植到另一个数据库，并期望它们能够工作。

在MySQL中，您可以使用update ... join语法。但是，您需要一个唯一标识每一行的主键列(或一组列)作为联接条件。假设列id是您的主键，则如下所示：

update db_name.table_name t
inner join (
    select id
    row_number() over (partition by col_a order by col_a) row_num 
    from db_name.table_name) 
) t1 on t1.id = t.id
set t.col_b = 'test' 
where t1.row_num > 1

边注：

正如我所理解的，

• ，您的查询是用来标记col_a上的重复的。但是在使用row_number()时，没有定义哪些重复行将被标记，因为您的order by子句不是确定性的；最好有一个over()子句，例如:在MySQL 8.0中只有

可以使用row_number()之类的函数

Answer 2

更新并删除mysql临时表中的“不工作”。所做的工作是使用临时表CTE连接原始表，并更新原始表：

    WITH cte as (select *, row_number() 
over (partition by col_a order by col_a) row_num 
from db_name.table_name) 
    
    update db_name.table_name T1 
Join 
cte on T1.col_a = cte.col_a set t1.col_b='test' where cte.row_num > 1

这个工作对删除指令也很好。