I am trying to remove the whitespace from my primary key column like so:
update EMAILS set email = TRIM(email);
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.