我想从我的数据库表中删除重复的行 [英] I want to delete duplicate rows from my database table

问题描述

我浏览了一些关于这个主题的答案，但没有一个在我的工作台上执行.我的表中有重复的行，我想删除所有仅保留其中之一的行.首先，我通过 Count 函数将它们分组，然后尝试删除它们，但它不起作用.我尝试从网站上的旧问题运行 CTE 查询:

I went through a few answers on this topic but none of them are executing on my workbench. I have duplicate rows in my table and I want to delete all retaining only one of them. First I grouped them by Count function and then tried deleting them but it isn't working. I tried running CTE query from old questions on the website:

 WITH ReferenceDP AS (
  SELECT[ReferenceId1], 
     row_number() OVER(PARTITION BY ReferenceId1 ORDER BY ReferenceId1) AS [rn]
  FROM TABLE
)
DELETE ReferenceDP WHERE [rn] > 1

但是没有用.我附上了我的桌子的图片.有人可以帮我查询.谢谢你.DuplicateRowCount

But it did not work. I have attached the image of my table. Can someone help me out with the query. Thank You. DuplicateRowCount

推荐答案

您可以使用删除连接查询并两次引用您的表.

You can use the delete join query and reference your table twice.

DELETE t1 FROM ast_fx_all_mst t1
INNER JOIN ast_fx_all_mst t2 
WHERE 
    t1.ReferenceId < t2.ReferenceId AND t1.ContactName = t2.ContactName;

这个链接可能有帮助:https://www.mysqltutorial.org/mysql-delete-加入/

这篇关于我想从我的数据库表中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！