MySQL: Delete Duplicate entries from a row not working

Question

I am trying to use the following code to remove duplicate entries from a column called herit. The name of my table is people

DELETE FROM people
 WHERE herit NOT IN (SELECT * 
                    FROM (SELECT MIN(n.herit)
                            FROM people n
                        GROUP BY n.herit) x)

Although this code is executed and it states "5 rows affected", it doesn't remove duplicates from my table in mysql.

What could possibly be wrong? I searched all over the internet, couldn't find anything satisfactory

_didn't commit your transaction?_ Where do you see any transactions? @shadowsheep — B001ᛦ, Mar 01 '18 at 11:09
You need to remove duplicate row or only column value???Please clear. — Sumit, Mar 01 '18 at 11:12
If you do `select * from people where herit not in...` do you get the row you wanna delete? — shadowsheep, Mar 01 '18 at 11:14
I need to remove all the rows which contain similar values for the column named herit @Sumit — Prasinus Albus, Mar 01 '18 at 11:16
So that's the reason. You must have the right query that shows you all the rows you wanna delete. Then transform it in a delete statement. — shadowsheep, Mar 01 '18 at 11:20
Your group by is probably not returning what you want can you add sample data and expected output to your question as text. And what's a herit other than an Ancient Egyptian princess of the Second Intermediate Period? — P.Salmon, Mar 01 '18 at 11:43
Does this find the `herit`s you want to delete? `SELECT n.herit FROM people n GROUP BY n.herit HAVING count(*) > 1` — Klas Lindbäck, Mar 01 '18 at 11:51
Possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) — P.Salmon, Mar 01 '18 at 12:24
Possible duplicate of https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Klas Lindbäck, Mar 01 '18 at 12:43

Sumit · Accepted Answer · 2018-03-01T12:43:52.373

1

Try this one..

DELETE FROM people WHERE id NOT IN 
(SELECT id FROM (SELECT id FROM people GROUP BY herit HAVING count(herit) > 1 
OR count(herit) = 1) newTable)

This is actual and resultant table

edited Mar 01 '18 at 12:43

answered Mar 01 '18 at 12:07

Sumit

1,702
2
14
20

2

Couldn't it be simplified to `DELETE FROM people WHERE id NOT IN (SELECT id FROM people GROUP BY herit)`? – Klas Lindbäck Mar 01 '18 at 13:14
1

No delete operation is not perform with same table using nested query. So we need to fool the mysql.. – Sumit Mar 01 '18 at 13:16

MySQL: Delete Duplicate entries from a row not working

1 Answers1