使用mysql查询删除表中的重复条目

In my database there is a table which consist redundant data. It means it is having the same data multiple times in multiple rows.

在我的数据库中有一个包含冗余数据的表。这意味着它在多行中多次具有相同的数据。

I want to delete all redundant data and keep only one record. The problem is my table has no primary or unique key now. But I have data in it.

我想删除所有冗余数据并只保留一条记录。问题是我的表现在没有主键或唯一键。但我有数据。

Could you give me an example of a MySQL query that delete all duplicate row ?

你能给我一个删除所有重复行的MySQL查询的例子吗?

DELETE 
FROM enr_money_handover_detail 
WHERE lastupdate IN (
                      SELECT lastupdate 
                      FROM enr_money_handover_detail 
                      GROUP BY lastupdate);

This shows the error like

这显示了错误

You can't specify target table 'enr_money_handover_detail' for update in FROM clause".

您不能在FROM子句中指定目标表'enr_money_handover_detail'以进行更新。

3 个解决方案

#1

SELECT DISTINCT is you friend. Create a new table and insert into from a query using select distinct from your old table.

SELECT DISTINCT是你的朋友。创建一个新表,并使用旧表中的select distinct从查询中插入。

#2

I think you need to take copy of all distinct records from your current table to some other table (backup).

我认为您需要将当前表中所有不同记录的副本复制到其他表(备份)。

And then truncate your original table and then copy back the records from your backup table to original table.

然后截断原始表,然后将备份表中的记录复制回原始表。

Below commands will help you to achieve it.

以下命令将帮助您实现它。

CREATE TABLE handover_backup SELECT DISTINCT * FROM enr_money_handover_detail;

TRUNCATE TABLE enr_money_handover_detail;

INSERT INTO enr_money_handover_detail SELECT * FROM handover_backup;

Hope this will help you.

希望这会帮助你。

#3

As someone else stated you can use Select Distinct, and use a simple Delete query this way:

正如其他人所说,您可以使用Select Distinct,并以这种方式使用简单的Delete查询:

DELETE FROM enr_money_handover_detail WHERE lastupdate IN (SELECT DISTINCT(lastupdate) FROM enr_money_handover_detail);

#1