Find duplicate rows, then remove one of them

Question

Possible Duplicate:
Remove duplicate rows in MySQL

I have the following query that shows duplicate rows:

SELECT ID, user_login, wp_users.user_email FROM wp_users
INNER JOIN (SELECT wp_users.user_email FROM wp_users
GROUP BY wp_users.user_email HAVING count(ID) > 1) dup ON wp_users.user_email = dup.user_email

I've only been able to find queries that remove both of them instead of just removing one of them (say the one with the highest ID).

Any ideas?

@MotiveKyle Check the accepted answer. Don't be sure until you check. — fancyPants, Sep 12 '12 at 14:29

score 1 · Answer 1 · answered Sep 11 '12 at 21:12

1

Please try this sql to delete duplicate rows:

delete from users where id not in (select min(id) from users group by user_email)

answered Sep 11 '12 at 21:12

udalmik

7,838
26
40

score 0 · Answer 2 · edited Sep 12 '12 at 00:16

0

DELETE FROM 
table_name A
WHERE  a.rowid >  ANY ( SELECT B.rowid
                        FROM table_name B
                        WHERE A.col1 = B.col1 AND 
                              A.col2 = B.col2
                       );

I guess this will work

edited Sep 12 '12 at 00:16

ClearLogic

3,616
1
23
31

answered Sep 11 '12 at 21:03

Naga

812
2
8
12

Find duplicate rows, then remove one of them

2 Answers2