DELETE duplicates from table where there are two variables to consider

Question

We have a table that is setup with an (auto Increment) ID, ClassID, StudentID. The class id is for the class that the student is taking. Sometimes our system creates duplicates of the same student in the same class. We are currently trying to fix that problem. It might have to do with hitting the back button.

Students will often take the next class so we don't want to delete duplicates of students. We want to delete duplicate students that are contained in the same classID.
For example:

ID | ClassID | StudentID
1  |   1     |     1
2  |   2     |     1
3  |   2     |     1
4  |   2     |     2
5  |   2     |     2

I want to delete IDs 3 and 5. I have searched the Internet for this answer and can't seem to find it. The best that I've found is grouping but how do I group each class id and find duplicates within each classID grouping?

The best solution is to travel back in time and apply a `unique(ClassID, StudentID)` constraint to the table. — user229044, Nov 02 '10 at 20:59
Ok, maybe I'm not understanding your problem, but aren't ID 2 and 3 the duplicated ones, and not 3 and 5??? — Chepech, Nov 02 '10 at 21:02

Vincent Savard · Answer 1 · 2010-11-02T21:22:13.823

I read an interesting article about something like this. As everybody knows, it is not a good thing to do a query like this one to remove duplicates :

SELECT ClassID, StudentID
FROM your_table
GROUP BY ClassID, StudentID;

In this case, DISTINCT would be the best solution. However, sometimes it is better to start with a bad syntax such as the one above to make a good query. First, let's select pairs that are a duplicate :

SELECT ClassID, StudentID
FROM your_table
GROUP BY ClassID, StudentID
HAVING COUNT(*) > 1;

As you may or may not know, you can't delete lines using a subquery in the DELETE query. You have to use a temporary table. The full code to do it is this one :

CREATE TEMPORARY TABLE keep_lines AS 
    SELECT MAX(id) AS id_to_keep -- you can use MIN if wanted
    FROM your_table
    GROUP BY ClassID, StudentID;

DELETE FROM your_table
WHERE id NOT IN (SELECT id_to_keep
                 FROM keep_lines);

DROP TABLE keep_lines;

Then, as many others stated, add a UNIQUE constraint to your table!

I think your temp table should be called `keep_lines` not `duplicate_lines` and since it seems he wants the LOWER of the id's, use `MIN()` and you forgot the `AS id_to_keep`. But - Great answer -- +1 — gnarf, Nov 02 '10 at 21:15
You're right, I'll rename the table. I also already added a comment about MIN and added the "id_to_keep". — Vincent Savard, Nov 02 '10 at 21:16

score 2 · Answer 2 · answered Nov 02 '10 at 21:02

2

You can not DELETE or UPDATE records of the same table you are using to reference. As such, you will either need to create a temporary table to use as the reference. Or create a PHP script that will fire off a DELETE command of your matching IDs.

Here is an example SQL query though:

SELECT MIN(ID) AS minID, ClassID, StudentID
FROM the_table GROUP BY ClassID, StudentID HAVING COUNT(StudentID) > 1

You could run this multiple times and it would continue to remove duplicates.

answered Nov 02 '10 at 21:02

Jason McCreary

71,546
23
135
174

Using PHP (or any other language than SQL) is a bit overkill in my opinion. PHP is slow, your RDBMS has been designed to do operations like this. – Vincent Savard Nov 02 '10 at 21:12
@Vincent, agreed. But the OP tagged PHP, so I mentioned it as an alternative to a temporary table. However, your answer is solid. +1 – Jason McCreary Nov 03 '10 at 01:32

Gingi · Answer 3 · 2010-11-02T21:13:50.743

0

You can use the following SQL statements to remove all but the earliest unique rows:

create temporary table unique_ids as
select min(id) as ID
  from some_table
 group by ClassID, StudentID;

delete some_table
  from some_table
       left join unique_ids using (id)
 where unique_ids.id is null;

If you're operating on a large table, consider adding an index after creating the temporary table.

edited Nov 02 '10 at 21:13

answered Nov 02 '10 at 21:04

Gingi

2,149
1
18
34

score 0 · Answer 4 · edited May 23 '17 at 12:21

0

You can find another approuch here. But the unique key constraint on ClassID and StudentID is something that you definitively need to do.

edited May 23 '17 at 12:21

Community

1
1

answered Nov 02 '10 at 21:05

Chepech

5,258
4
47
70

score 0 · Answer 5 · answered Nov 02 '10 at 21:09

I'd strongly suggest a solution using a temporary table. Easy, fast, and no hazzle with complex queries. Just create a similar table (maybe type=MEMORY for speed), then insert all rows using a simple select distinct query, truncate the original table and replace the table data with the data from the temporary table.

Of course, this only works for databases which can be taken out of production for the duration.

DELETE duplicates from table where there are two variables to consider

5 Answers5