Deleting duplicate rows with two specific column duplicates

Question

I'm trying to delete duplicate rows from my database, that has the same 'input' column, and 'response' column. Right now, I have a query that SELECT all the duplicates, but I'm not sure how to write the query to delete the duplicates:

SELECT * , COUNT( * ) AS matches 
FROM allData 
GROUP BY input, response 
HAVING matches > 1

If I'm writing the DELETE query, I think it would something like

DELETE FROM allData WHERE blah = blah

^---But that doesn't let me select the 'count(*)' or 'group by', so I'm not really sure how to properly write this.

What is your primary key? – Paul Spiegel May 26 '16 at 01:55 — Paul Spiegel, May 26 '16 at 01:55

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

Try this;)

DELETE 
    T1
FROM allData T1
INNER JOIN (
    SELECT input, response
    FROM allData 
    GROUP BY input, response 
    HAVING COUNT(1) > 1 
) T2 ON T1.input = T2.input AND T1.response = T2.response

Edited:

DELETE T1
FROM allData T1
INNER JOIN allData T2 ON T1.input = T2.input AND T1.response = T2.response AND T1.id > T2.id

This will remain the record which has the minimum id. And take a look of Delete all duplicate rows except for one in mysql.

edited May 23 '17 at 12:24

Community

1
1

answered May 26 '16 at 01:42

Blank

12,308
1
14
32

Nope. This deletes ALL the rows that have duplicates of themselves. – frosty May 26 '16 at 01:50
I need to leave ONE original not deleted. – frosty May 26 '16 at 01:50
Good thing I tried this on a test table first...lol – frosty May 26 '16 at 01:50
@frosty you want to remain which record? – Blank May 26 '16 at 01:50
`ONE original` is which record? – Blank May 26 '16 at 01:53
Reno, for example: if there are a total of three rows in the database that are duplicated of themselves, when I run this query, it deletes ALL THREE rows. – frosty May 26 '16 at 01:59
I need at least one row to not be deleted. – frosty May 26 '16 at 01:59
Is there any other column exists? Primary key column is ok – Blank May 26 '16 at 02:00
No other columns exists in the test table, except the id. – frosty May 26 '16 at 02:01
OK, that is enough. – Blank May 26 '16 at 02:02
That's just the test table though, in my original table, there are definitely more columns. – frosty May 26 '16 at 02:03
Check my **Edited** answer please. – Blank May 26 '16 at 02:06

Deleting duplicate rows with two specific column duplicates

1 Answers1