SQL: Remove rows with a duplicate field

Question

I have duplicate entries due to a programming error.

Table Example:

id | group | userNum | username | name
---------------------------------------
1  | AA11  |   D-01  |   user1  | Donald
2  | AA11  |   D-02  |   user2  | Cruz
3  | AA11  |   D-03  |   user3  | Rubio
4  | AA11  |   D-01  |   user1  | Donald    <------DUPLICATE
5  | AA11  |   D-04  |   user4  | Cruz
6  | AA22  |   D-03  |   user2  | Rubio
7  | AA22  |   D-02  |   user1  | Donald

userNum, username must be unique to each group, but a group can have userNum, username, name which are found in other groups.

Which database are you using? There are several different ways to do this. — sgeddes, Mar 03 '16 at 18:50
Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Emmet, Mar 03 '16 at 18:54
Once you resolve this issue, alter your table to include the constraint for the primary key which includes userNum and username. So that this kind of errors will not appear in future. — Harish Talanki, Mar 03 '16 at 18:57

score 1 · Accepted Answer · answered Mar 03 '16 at 18:55

A standard SQL approach is to use a subquery in the WHERE clause. For instance:

delete from example
    where id not in (select min(id)
                     from example e2
                     group by group, userNum, username, name
                    );

This doesn't work in MySQL. You can do something similar using left join:

delete e
    from example e left join
         (select min(id) as minid
          from example e2
          group by group, userNum, username, name
         ) ee
         on e.id = ee.minid
    where ee.minid is null;

score 0 · Answer 2 · answered Mar 03 '16 at 18:54

0

Add group by userNum, username, group at the end of your query..

answered Mar 03 '16 at 18:54

cosmos

2,263
1
17
30

SQL: Remove rows with a duplicate field

2 Answers2