Deleting duplicate values from a mysql table but keep one

Question

I'm trying to delete duplicate rows from a mysql table, but still keep one. However the following query seemingly deletes every duplicate row and I'm not sure why. Basically I want to delete the row if the outputID, title and type all matches.

DELETE DupRows.*
FROM output AS DupRows
INNER JOIN (
    SELECT MIN(Output_ID) AS Output_ID, Title, Type
    FROM output
    GROUP BY Title, Type
    HAVING COUNT(*) > 1
) AS SaveRows 
    ON  SaveRows.Title = DupRows.Title 
    AND SaveRows.Type = DupRows.Type
    AND SaveRows.Output_ID = DupRows.Output_ID;

Incidentally, it's often quicker to create a new table with just the data you want to keep — Strawberry, Feb 12 '19 at 01:13
Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Nick, Feb 12 '19 at 01:32

GMB · Answer 1 · 2019-02-12T01:20:31.123

1

Just :

DELETE DupRows
FROM output AS DupRows
INNER JOIN output AS SaveRows
    ON  SaveRows.Title = DupRows.Title 
    AND SaveRows.Type = DupRows.Type  
    AND DupRows.Output_ID > SaveRows.Output_ID

This will delete all duplicates on Title and Type while keeping the record with the lowest value.

If you are running MySQL 8.0, you can use window function ROW_NUMBER() to assign a rank to each record in Title/Type groups, ordered by id. Then you can delete all records whose row number is not 1.

DELETE FROM output
WHERE Output_ID IN (
    SELECT Output_ID
    FROM (
        SELECT Output_ID, ROW_NUMBER() OVER(PARTITION BY Title, Type ORDER BY Output_ID) rn
        FROM output
    ) x
    WHERE rn > 1
)

edited Feb 12 '19 at 01:20

answered Feb 12 '19 at 00:59

GMB

216,147
25
84
135

@Nick : right, this has to be a quite common question... However, I am checking now and I cannot really find a high voted post for this... – GMB Feb 12 '19 at 01:38
Check the one I flagged, it has 300+ upvotes for the question – Nick Feb 12 '19 at 01:39
@Nick : ok, I voted to close. Just one thing, as of 2015 `ROW_NUMBER()` was not available... In this regard my answer might be useful. – GMB Feb 12 '19 at 01:48
I am wondering... Would it make sense to add another answer to the original 2015 post to demonstrate how to use `ROW_NUMBER()` in MySQL8 ? – GMB Feb 12 '19 at 01:50
I did find an [answer using row_number](https://stackoverflow.com/questions/39564777/need-to-delete-duplicate-records-from-the-table-using-row-number) but there's not many so it probably is worthwhile. – Nick Feb 12 '19 at 01:56
@Nick : answer updated. I never posted on a question that old before, feel like an archeologist right now ! Thanks for your guidance. – GMB Feb 12 '19 at 02:07

score 0 · Answer 2 · answered Feb 12 '19 at 02:56

0

Delete From output  Where Output_ID NOT IN (
Select MIN(Output_ID) from output  Group By Title, Type  Having COUNT(*)>1
)

answered Feb 12 '19 at 02:56

Mohammad Shahjada Sagor

56
2

score 0 · Answer 3 · answered Feb 12 '19 at 05:54

By below query duplicate rows with matching condition get deleted and keeps one oldest unique row.

NOTE:- In my query I used id column is auto increment column.

DELETE t1
FROM output  t1, output  t2
WHERE t1.Title  = t2.Title
AND  t1.Type   = t2.Type 
AND t1.Output_ID   = t2.Output_ID 
AND t1.id>t2.id

If you want to keep newly inserted unique row just change the last condition as:

DELETE t1
 FROM output  t1, output  t2
 WHERE t1.Title  = t2.Title
 AND  t1.Type   = t2.Type 
 AND t1.Output_ID   = t2.Output_ID 
 AND t1.id<t2.id

Deleting duplicate values from a mysql table but keep one

3 Answers3

Linked

Related