Remove duplicate rows from MySql

Question

This is my mysqSql table.

m_Id |  m_Mobile |  m_ReceiveFromBranch
________________________________________
1       12345       1
2       12345       5
3       12345       1   //->Duplicate of m_Id 1
4       99999       1
5       88888       2
6       88888       5
7       88888       2   //->Duplicate of m_Id 5

...

How can I delete only duplicate rows? I have more that 10 thousand rows with many duplicates.

You can follow this url: http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Web Artisan, May 12 '17 at 06:37
@BikashPaul I want to keep the rows with `m_Mobile` with distinct `m_ReceiveFromBranch` — A B Catella, May 12 '17 at 06:41
Usually you'd solve this with an exists clause, but I am not able to post my answer anymore. Anyway, the solution linked to works, too. — Thorsten Kettner, May 12 '17 at 06:54

Sunny Khatri · Answer 1 · 2017-05-12T07:03:22.810

1

Try this

 DELETE a
FROM mytable  as a, mytable  as b
WHERE
  (a.m_mobile   = b.m_mobile )
AND (a.m_ReceiveFromBranch = b.m_ReceiveFromBranch )
AND a.ID < b.ID;

edited May 12 '17 at 07:03

answered May 12 '17 at 06:39

Sunny Khatri

537
7
15

How can I delete ? – A B Catella May 12 '17 at 06:42
Check : DELETE a FROM mytable as a, mytable as b WHERE (a.m_mobile = b.m_mobile ) AND (a.m_ReceiveFromBranch = b.m_ReceiveFromBranch ) AND a.ID < b.ID; – Sunny Khatri May 12 '17 at 07:03

Remove duplicate rows from MySql

1 Answers1