Removing double spaces in usernames directly in the SQL

A client using the Bulk invite module (by Agaric's David Valdez) had invited hundreds of users with slightly malformed usernames— two spaces between first and last name when there should only be one.

Note that you need to do the change both in the users_field_data table and the users_field_revision table— and you need to clear caches when you're done to see the change on user edit pages, which is where it matters.

Check for the response! For example, the first time i ran this it returned:

ERROR 1062 (23000): Duplicate entry 'John Doe-en' for key 'user__name'

This was because the same name was there twice (once with a single space and once with a double space) associated with different e-mail addresses (but almost certainly the same person). Rather than make the decision of which e-mail address should be used for this user, it was easiest to rename the existing username for the second address:

UPDATE users_field_data SET name = REPLACE(name,'John Doe','JohnDoe');

And then the original command could complete, like this:

And then the users_field_revision table, in our case still with the extra step:

Then exit MySQL/MariaDB/whathaveyou and clear caches: