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;