0

I'm trying to set the wp_users.display_name for a rather larger users db using SQL query.

Got till this point:

SELECT x.display_name, x.first_name, x.last_name
FROM
(SELECT
  u.display_name,
  f.meta_value AS 'first_name',
  l.meta_value AS 'last_name'
FROM
  wp_users AS u
INNER JOIN
  wp_usermeta f ON f.user_id = u.ID
INNER JOIN
  wp_usermeta l ON l.user_id = u.ID
WHERE f.meta_key = 'first_name'
AND l.meta_key = 'last_name') AS x;

But trying to change the first SELECT statement to an UPDATE doesn't work:

UPDATE
  wp_users
SET
  display_name = CONCAT(x.first_name, ' ', x.last_name)
FROM
(SELECT
  u.display_name,
  f.meta_value AS 'first_name',
  l.meta_value AS 'last_name'
FROM
  wp_users AS u
INNER JOIN
  wp_usermeta f ON f.user_id = u.ID
INNER JOIN
  wp_usermeta l ON l.user_id = u.ID
WHERE f.meta_key = 'first_name'
AND l.meta_key = 'last_name') AS x;
2
  • 1
    Do you have access to WP CLI? If so this can be made much easier/faster, a raw SQL query won't invalidate caches or fire hooks which can cause problems. That SQL query also won't leave a separator between the first and last names
    – Tom J Nowell
    Dec 18, 2020 at 14:35
  • Thanks for the tip Tom. I just installed WP CLI. And what would be the WP CLI command to reset the display_name for all users?
    – Nitin Nain
    Dec 18, 2020 at 16:08

1 Answer 1

1

Figured out the SQL query that can be used to reset the display_name to first_name + last_name. And it's simpler than the JOINs that I was trying when I posted the question.

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
    )
); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.