Set Display Name to first and last name (phpmyadmin SQL Query)

Question

I'm trying to change all the users Display Names to be their first and last name. I have looked into a number of plugins and wrote my own PHP to try and accomplish this however the site has over 50,000 registered users (I can imagine most are spam from my clients previous site) but because of this the PHP hangs and only gets to about 2000 before giving up.

So I thought that the next best thing would be to write an SQL statement from within PHPmyAdmin to perform the task, However pure SQL confuses the hell out of me and so far all I have been able to do is set the display name to be the users First name I'm not to sure how I would go about getting the Last name as well. Here is the statement that I used.

UPDATE wp_users, wp_usermeta
SET display_name = meta_value
WHERE ID = user_id
AND meta_key = 'first_name'

Any help would be appreciated.

Answer 1

I know this is an old question, but for anyone still looking how to achieve this, the following query should do the job:

UPDATE wp_users
INNER JOIN wp_usermeta AS fn_meta ON wp_users.ID = fn_meta.user_id AND fn_meta.meta_key = 'first_name'
INNER JOIN wp_usermeta AS ln_meta ON wp_users.ID = ln_meta.user_id AND ln_meta.meta_key = 'last_name'
SET wp_users.display_name = CONCAT(fn_meta.meta_value, ' ', ln_meta.meta_value)

Basically you need to add two user meta joins - one to retrieve first name and another for last name, since they are stored in separate rows.

I would highly recommend testing this on a testing/staging server before running it in production.