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?

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?

• users
• sql

• "doesn't seem to have changed" - does it change or not? You should be able to see and test this from PHPMyAdmin – kero Commented Feb 9, 2021 at 15:33
• Sorry I should have been more clear, in PhpMyAdmin the column has been updated but in the Wordpress user profile it's still the same as before and doesn't appear to have changed. – jamesmonk83 Commented Feb 9, 2021 at 16:36

1 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.