如何从我的数据库中删除重复的记录 [英] how can I delete duplicate records from my database

问题描述

我使用此查询列出重复记录

  SELECT DISTINCT column1从表
 group by column1 HAVING count（column1）> 1 
  

用于特定查询

  SELECT DISTINCT通过ReportingDetailID从reportingdetail组中ReportingDetailID HAVING计数（ReportingDetailID）> 1; 
  

现在我需要删除重复的条目，最后才能表现出漂亮和干净的表格。

在此查询

  DELETE FROM table_name 
 USING table_name，table_name AS vtable 
 WHERE 
 （table_name.id> vtable.id）
 AND（table_name.req_field = req_field）
  

根据查询作者替换req_field和table_name - 应该工作没有任何问题

我只是不明白如何让它在我的db

我的表格名称为 reportingdetail ，列名称为 reportingdetailID 。

  + ------------------- + 
 | ReportingDetailID | 
 + ------------------- + 
 | 664602311 | 
 | 664602311 | 
 | 664602311 | 
 | 664602311 | 
 + ------------------- + 
  

$ b b

删除所有重复但保留一个

谢谢

解决方案

删除重复行的基本原理...

  CREATE TEMPORARY TABLE tmptbl AS SELECT DISTINCT * FROM myTabs; 
 DELETE FROM myTabs; 
 INSERT INTO myTabs SELECT * FROM tmptbl; 
  

对于上面我有 myTabs Id 作为 Id 重复的列。

根据您的要求更改...

I'm using this query to list duplicate records

SELECT DISTINCT column1 from table
group by column1 HAVING count(column1) > 1

for my specific query

SELECT DISTINCT ReportingDetailID from reportingdetail group by ReportingDetailID  HAVING count(ReportingDetailID) > 1;

works great!

now I need to delete duplicate entries, to end up with nice and clean table

I'm looking at the query here

DELETE FROM table_name 
USING table_name, table_name AS vtable 
WHERE 
    (table_name.id > vtable.id) 
AND (table_name.req_field=req_field)

According to query author "Replace req_field and table_name - should work without any issues"

I just don't understand how can I get it to work on my db

my table name is reportingdetail and column name is reportingdetailID

+-------------------+
| ReportingDetailID |
+-------------------+
|         664602311 | 
|         664602311 | 
|         664602311 | 
|         664602311 | 
+-------------------+

Delete all duplicates but keep one

Thank you

解决方案

The basic principle to delete the duplicate rows...

CREATE TEMPORARY TABLE tmptbl AS SELECT DISTINCT * FROM myTabs;
DELETE FROM myTabs;
INSERT INTO myTabs SELECT * FROM tmptbl;

For above I have myTabs as Table with Id as column where Id were duplicated.

Please do changes as per your requirement...

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