How do I delete duplicate rows in SQL?

Question

I am trying to delete duplicate rows using SQL, whilst leaving one of the rows behind.

The table I am trying to delete duplicate rows from is called table "A" made up of:

A.AID, A.BID, A.AName, A.AType, A.APrice.

In this table I have a number of duplicate rows with all of the data exactly the same apart from the A.ID.

I am trying to create a query that will look for duplicates and then remove the duplicate making sure one of the rows are left behind. I am using phpMyAdmin and MySQL.

While maybe not the most elegant solution... if you can find the duplicate rows, you can simply delete them with a "limit" clause. For example if you locate 3 duplicate records given some condition, then "DELETE FROM TABLE WHERE (SOMETHING) LIMIT 2". That would delete two of them, leaving the third. — DragonYen, Apr 29 '15 at 14:17
`ALTER IGNORE TBLNAME ADD UNIQUE_INDEX` is probably the fastest way. — Daan, Apr 29 '15 at 14:18
This question has been answered dozens and dozens of times. Here is one. http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Sean Lange, Apr 29 '15 at 14:18

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

use group by and count

edited May 23 '17 at 12:07

Community

1
1

answered Apr 29 '15 at 14:17

backtrack

7,996
5
52
99

score 1 · Answer 2 · answered Apr 29 '15 at 14:19

DELETE FROM
    YourTable
WHERE
    AID NOT IN ( SELECT
                    MAX(AID)
                 FROM
                    YourTable
                 GROUP BY
                    BID ,
                    AName ,
                    AType ,
                    APrice );

score 1 · Answer 3 · answered Apr 29 '15 at 14:23

1

Consider the query below, this will remove all the duplicate rows and prevents any future duplicate row.

ALTER IGNORE TABLE A ADD UNIQUE INDEX index_name (A.AID, A.BID, A.AName, A.AType, A.APrice );

answered Apr 29 '15 at 14:23

Sid

14,176
7
40
48

How do I delete duplicate rows in SQL?

3 Answers3