错误1288，删除重复行时数据库不可更新

oknwwptz 于 2021-07-26 发布在 Java

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

+----+-----------+
| ID | name      |
+----+-----------+
|  1 | fooo      |
|  2 | FWAEFAWEF |
|  3 | test      |
|  4 | test      |
|  5 | test      |
+----+-----------+

这是im使用的数据库表（t1），我尝试删除重复的行，只留下一个唯一的行，我尝试在admin中使用公共表表达式

mysql> WITH cte AS (
    -> SELECT
    -> ID,
    -> name,
    -> ROW_NUMBER() OVER (
    -> PARTITION BY
    -> name
    -> ORDER BY
    -> name
    -> ) row_num
    -> FROM
    -> discord.t1
    -> )
    -> DELETE FROM cte
    -> WHERE row_num >1;
ERROR 1288 (HY000): The target table cte of the DELETE is not updatable

我遵循一个关于删除重复表的教程，所以我从中得到了查询，但是我不确定是否应该链接它

sql mysql Database

来源：https://stackoverflow.com/questions/62940863/err-1288-db-not-updatable-on-deleting-duplicate-rows

2条答案

按热度按时间

oogrdqng1#

使用自联接：

delete t1
from tablename t1 inner join tablename t2
on t2.name = t1.name and t2.id < t1.id;

请看演示。
结果：

| ID  | name      |
| --- | --------- |
| 1   | fooo      |
| 2   | FWAEFAWEF |
| 3   | test      |

赞(0）回复(0）举报 2021-07-26

ybzsozfc2#

一种方法使用聚合：

delete t1
     from discord.t1 t1 left join
          (select name, min(id) as min_id
           from discord.t1
           group by name
          ) tt
          on t1.id = tt.min_id
    where tt.min_id is null;   -- no match

赞(0）回复(0）举报 2021-07-26

我来回答

错误1288，删除重复行时数据库不可更新

2条答案

相关问题

热门标签

最新问答