-2

I have a MySQL table with 80,000 records. One of the columns is "email". I'd like to keep all 80,000 rows, but empty out each duplicate email value. Is this possible in MySQL directly?

Thanks in advance!

yycroman
  • 7,511
  • 1
  • 19
  • 21
  • Possible duplicate of [MySQL Select only the redundant rows and leave out the original rows](https://stackoverflow.com/questions/44484866/mysql-select-only-the-redundant-rows-and-leave-out-the-original-rows) – Progman Sep 17 '18 at 21:04
  • Or https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql or https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql/854177 – Progman Sep 17 '18 at 21:05
  • Just out of curiosity, why? Can't you use a `DISTINCT` modifier in your queries? – Nick Sep 17 '18 at 22:41
  • [deleting dups](https://stackoverflow.com/questions/49938359/remove-duplicate-data-from-sql/49938519#49938519) is useful if you have a criteria on elimination. And after the data is clean add a `UNIQUE KEY` – danblack Sep 17 '18 at 23:49

1 Answers1

1

Assuming you want to remove the higher id from the email_table.

DELETE e1 
FROM  email_table e1, email_table e2
WHERE e1.id > e2.id AND 
      e1.email = e2.email;

And enforce it for the future:

ALTER TABLE email_table ADD UNIQUE KEY email_unique(email);
danblack
  • 12,130
  • 2
  • 22
  • 41