Mysql query for removing rows having duplicate entries

Question

I want to delete all rows which has duplicate entries and I do not want to keep the single one of each. I wish to remove all entries having duplicate entries. Here I referred some code which will keep highest or lowest id value. All queries here will remain a single entry from duplicate records

Mysql Query I tried is

DELETE * ,count(*)as n FROM cart by rfid HAVING n>1

DB design :

ID(PK)|RFID    |CATAGORY                                                       
1     |1       |5                                                                                           
2     | 1      | 5                                                                                        
3     | 2      | 4                                                                                       
4     |3       | 6

Output:

ID(PK)|RFID|CATAGORY                                                                       
2     | 1      | 5                                                                                        
3     | 2      | 4                                                                                       
4     |3       | 6

Expected Result:

ID(PK)|RFID|CATAGORY                                                               
 3     | 2      | 4                                                                                        
 4     |3       | 6

Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — sandwood, May 02 '18 at 09:37
I think this question is duplicate try this https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — loki, May 02 '18 at 09:37
But it will not delete all records, will remain a single entry having duplicate values. I wish to delete complete records having duplicate entries — Akhila Bhaskar, May 02 '18 at 09:44

koalaok · Accepted Answer · 2018-05-02T15:25:31.073

Select distinct and oldest entries (Note: using MIN() and assuming id is autoincrement mode and PK) , just switch to MAX() to preserve newest instead.

SELECT MIN(id) FROM cart group by rfid;

All together (deleting all that where not in previous select

DELETE FROM cart where id NOT in (SELECT MIN(id) FROM cart group by rfid);

The above wont work because You can't specify target table for update in FROM clause (DELETE in this case), but you can trickit around by a subselect, so the final working query would be:

DELETE FROM cart WHERE id NOT IN (SELECT MIN(A.id) FROM (SELECT * FROM cart) A group by A.rfid);

Edit (to remove all entries that have a count>1 )

DELETE FROM cart WHERE id  IN( 
  SELECT A.id FROM (SELECT * FROM cart) A where A.rfid IN 
  (SELECT B.rfid from  (SELECT * FROM cart) B group by B.rfid having count(B.rfid)>1)
);

score 0 · Answer 2 · edited May 02 '18 at 15:29

0

DELETE cart.* FROM cart INNER JOIN (SELECT rfid,catagory FROM cart GROUP BY rfid,catagory HAVING COUNT(*)>1) dup ON cart.rfid = dup.rfid AND cart.`catagory` = dup.`catagory`

This query solved my problem.thank u

edited May 02 '18 at 15:29

Lelio Faieta

6,457
7
40
74

answered May 02 '18 at 15:17

Akhila Bhaskar

11
5

Mysql query for removing rows having duplicate entries

2 Answers2