Remove duplicate entries in MySQL

Question

I have a table that has multiple entries that are the same and I'd like to remove the duplicate entries but keep the most recent record if that makes sense.

I have the unique ID column and the one I want to have only 1 of the same integer in is the 'owner' column as there is no need for there to be more than one.

Please include your table structure and ideally, an attempt of your own. — George, Jun 11 '14 at 13:54
second answer here: http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — Steve, Jun 11 '14 at 13:55

score 7 · Answer 1 · answered Jun 11 '14 at 13:58

You can just create a temporary table and put data you want there. Then delete all data in original table and insert data from temporary table. Like:

CREATE TABLE TMP (SELECT DISTINCT * FROM <ORIGINAL_TABLE>);
TRUNCATE TABLE <ORIGINAL_TABLE>;
INSERT INTO <ORIGINAL_TABLE> (SELECT * FROM TMP);

score 2 · Answer 2 · answered Jun 11 '14 at 14:02

2

DELETE FROM table WHERE `id` NOT IN (
    SELECT `id` FROM table 
    GROUP BY `id`
    ORDER BY `date` DESC
    LIMIT 1
);

answered Jun 11 '14 at 14:02

Bas van Dorst

6,632
3
17
12

The subquery only returns one ID, so this deletes everything except that one ID. It doesn't delete within groups. – Barmar Aug 24 '20 at 15:15

Remove duplicate entries in MySQL

2 Answers2