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;