MySQL Removing duplicated set of rows

Question

I'm new here and need help (first of all sorry for my english).

I've got a table like:

 P |  A  |  B  |  C 
--------------------
 1 | 222 | 333 | 444
 2 | aaa | bbb | ccc
 3 | 222 | 333 | fff
 4 | 222 | 555 | ggg

I consider row 1 and 3 as duplicated becouse columns A and B are the same in these rows so i would like to remove them. Do you know a query to remove (leave one ofcourse) these duplicates?

Thanks for any help :)

Please, use search. http://stackoverflow.com/q/854128/995958 • http://stackoverflow.com/q/2273526/995958 • http://stackoverflow.com/q/2807071/995958 • http://stackoverflow.com/q/4632445/995958 — lorenzo-s, May 18 '12 at 16:07

score 2 · Accepted Answer · edited May 18 '12 at 19:59

2

This could work:

delete from Table
where p in (
    SELECT min(p),a,b,c (or max)
    FROM table
    HAVING COUNT(a) > 1 and COUNT(b) > 1 )

edited May 18 '12 at 19:59

Kevin Bedell

13,254
10
78
114

answered May 18 '12 at 16:12

Minus

729
8
20

I see how can it work and it was helpful with many other table modifications, but I stoped the query after about 10hrs of running - table has over 3,8 mln entries it just took to long. I managed it with PHP. – McAbra Jun 10 '12 at 07:16

MySQL Removing duplicated set of rows

1 Answers1