Remove duplicate entry in a table using mysql query

Question

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 ?

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".

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". — Anant Padhi
– Anant Padhi, Commented May 7, 2015 at 14:07
:- instead of put code in comment, edit your question. This time I will edit your question to add code. — HaveNoDisplayName
– HaveNoDisplayName, Commented May 7, 2015 at 14:09
Still struggling? If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented May 7, 2015 at 14:15

Barry Keepence · Accepted Answer · 2015-05-07 13:24:57Z

2

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

answered May 7, 2015 at 13:24

Barry Keepence

1365 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nilesh Thakkar · Accepted Answer · 2015-05-08 06:16:42Z

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.

edited May 8, 2015 at 6:16

answered May 7, 2015 at 13:26

Nilesh Thakkar

1,4594 gold badges27 silver badges37 bronze badges

2 Comments

HaveNoDisplayName Over a year ago

I think back up table should have all data instead of DISTINCT data, When you transfering data from Backup table to Original table then use Distinct... Other wise there is an change to lost data.

Nilesh Thakkar Over a year ago

We can do either way, the ultimate goal is to delete duplicate entries and both will solve the purpose.

Dave Plug · Accepted Answer · 2015-05-07 14:13:40Z

-1

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

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

edited May 7, 2015 at 14:13

answered May 7, 2015 at 13:39

Dave Plug

1,0891 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Remove duplicate entry in a table using mysql query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related