删除具有两个特定列重复项的重复行[重复]

This question already has an answer here:

这个问题在这里已有答案:

Remove duplicate rows in MySQL 17 answers

删除MySQL 17答案中的重复行

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所有重复项,但我不知道如何编写查询来删除重复项:

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查询,我认为它会像

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. Any help would be great.

^ ---但是这不允许我选择'count(*)'或'group by',所以我不确定如何正确地写这个。任何帮助都会很棒。

1 个解决方案

#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.

这将保留具有最小ID的记录。看看删除除mysql中的一行之外的所有重复行。

#1