SQL Bulk update all Wordpress user's nicknames to firstname + lastname format

Question

I'm trying to bulk update all my users on one of my Wordpress sites to so that their nicknames aren't the default username but have the format firstname + lastname instead.

I've put together this code and have run it in PhpMyAdmin but it doesn't seem to have changed anything (although I get no errors):

UPDATE wp_users SET user_nicename = CONCAT(
    (
        SELECT meta_value FROM wp_usermeta 
            WHERE meta_key = 'first_name' AND user_id = ID
    ),
    ' ', 
    (
        SELECT meta_value FROM wp_usermeta 
            WHERE meta_key = 'last_name' AND user_id = ID
    )
);

Can anyone see where I'm going wrong with this?

Answer 1

You are updating the wrong column. user_nicename is used for the permalink of authors (so you really should undo your change if already done so in the actual database). If you want to change how usernames are displayed, update display_name instead.

UPDATE wp_users 
SET display_name = CONCAT(
    (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'first_name' AND user_id = ID),
    ' ',
    (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'last_name' AND user_id = ID)
);

I'm unsure if first_name and last_name can ever be NULL. If so, this query will fail. A more robust version would be

UPDATE wp_users 
SET display_name = CONCAT(
    COALESCE((SELECT meta_value FROM wp_usermeta WHERE meta_key = 'first_name' AND user_id = ID), ''),
    ' ',
    COALESCE((SELECT meta_value FROM wp_usermeta WHERE meta_key = 'last_name' AND user_id = ID), '')
);

Using COALESCE(col, '') will avoid any problems with NULL columns.