CODEWITHSUNDEEP
mysqlsqldatabasesql-updateduplicates
John Roberts
John Roberts

Reputation: 5966

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?

Upvotes: 1

Views: 828

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

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:

DELETE
FROM emails AS delete
INNER JOIN emails AS original
   ON TRIM(delete.email) = original.email
   AND delete.email <> original.email

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.

Upvotes: 1

Related Questions