-1

I allready found part of the solution in the following thread:

Removing duplicate email address based on the lowest id in mysql

I have this MYSQL table that contains also email addresses from which i want the duplicates to be removed witch leaving the duplicate with the highest ID intact. There are also many empty email-fields that should be ignored and thus preserved. After that no duplicate entries should ever be allowed for the emailfield in the future.

How can i achieve this? My head is absolutely spinning over this and i can'figure it out. I want to do this from within phpmyadmin since it should be a onetime operation only.

Coming from the following link: MySQL remove duplicates from big database quick

I tried:

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

But that gave me a syntax error concerning the select * statement (3rd line).

Community
  • 1
  • 1
ingridsede
  • 329
  • 1
  • 5
  • 19

1 Answers1

1

Try :

insert into tmp (email) select X.email from ((SELECT email FROM yourtable WHERE LENGTH(email) = 0) UNION (SELECT email FROM yourtable WHERE LENGTH(email) > 0 GROUP BY email ORDER BY id DESC)) AS X;
Tanatos
  • 1,857
  • 1
  • 13
  • 12