John Roberts John Roberts - 2 years ago 92
MySQL Question

Update primary key - if duplicate, then delete it

I am trying to remove the whitespace from my primary key column like so:

update EMAILS set email = TRIM(email);

However, in the case that the trimmed email is a duplicate, I want to simply delete it. Is there any way to do this?

Answer Source

There is not a great way to do what you suggest in a single query as MySQL is not really designed to accommodate a case like this where the initial primary key data was not properly sanitized before being inserted in the first place.

My suggestion would be to perform a separate query before this update that would identify and remove these potential records. You could do that like this:

FROM emails AS delete
INNER JOIN emails AS original
   ON TRIM( =
   AND <>

This would remove all those rows from the table that when trimmed would result in a primary key conflict. This query may take a while to run depending on the size of your table, since you would not be able to leverage an index for the TRIM()-based JOIN criteria.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download