-1

I have a database of around 126K entries, It has many entries having duplicate emails. I want to delete all the entries with duplicate ones and just the keep the one that was created earliest.

I tried using the following query but it takes forever and maxes out my server bandwidth.

Can anyone help? 

DELETE n1 FROM table n1, table n2 WHERE n1.ID > n2.ID AND n1.email = n2.email


Database Specifications

  1. PhpMyAdmin Version information: 3.5.3
  2. Server: Localhost via UNIX socket
  3. Software: MySQL
  4. Software version: 5.0.67-community - MySQL Community Edition (GPL)
  5. Protocol version: 10
  6. Server charset: UTF-8 Unicode (utf8)
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Omer Mujtaba
  • 121
  • 5

1 Answers1

-1

Maybe you can create a temporary table with unique emails and after delete all the email whose are more than once in the database .

CREATE TEMPORARY TABLE tab_temp
SELECT * FROM table GROUP BY email;

Then 

DELETE FROM table
WHERE id NOT IN ( SELECT id FROM tab_temp );

With that you can delete those that are repeated

aldairEZ
  • 1
  • 1
  • `1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery` I get this error when I run the second query. – Omer Mujtaba May 26 '19 at 21:17