HELP - Using UPDATE with SELECT and CONCAT

[doyoonk]

In WordPress there's a table called wp_usermeta which stores some of user information... which has following structure:

 

umeta_id    user_id    meta_key      meta_value

--------------------------------------------------------

5000          100          first_name    John

5001          100          last_name      Doe

5002          100          nickname      John Doe

5003          101          first_name    Tom

5004          101          last_name    McDonald

5005          101          nickname     

--------------------------------------------------------

 

What I'm trying to do is, for users that have blank 'meta_value' for the 'meta_key = nickname', I'm trying to UPDATE the record by using CONCAT of 'first_name' and 'last_name'.

 

I've got to the point where I can SELECT the CONCATednated value by using the following SQL statement but I can't seem to use this to update 'meta_value' for the 'meta_key = nickname':

SELECT CONCAT(a.meta_value, ' ', b.meta_value)

FROM wp_usermeta as a,wp_usermeta as b WHERE a.meta_key='first_name' AND b.meta_key='last_name' AND a.user_id = b.user_id

 

Please let me know if you have any idea how to achieve this.

 

Thanks in advance!

[Barand]

try

UPDATE wp_usermeta a
INNER JOIN wp_usermeta b ON a.user_id = b.user_id AND b.meta_key = 'first_name'
INNER JOIN wp_usermeta c ON a.user_id = c.user_id AND c.meta_key = 'last_name'
SET a.meta_value = CONCAT(b.meta_value, ' ', c.meta_value)
WHERE a.meta_key = 'nickname' AND a.meta_value IS NULL;

[doyoonk]

Thanks for the reply!

 

I just tried but there were 0 rows affected.  I checked the records but the previously blank nickname fields did not have the "first_name last_name" saved, even though first_name/last_name do exist for those particular users.

 

Is there something I should modify to get it working?

 

Thank you very much!

[Barand]

Are they NULL or are they '' originally?

 

Adjust the sql accordingly.

[doyoonk]

try

UPDATE wp_usermeta a
INNER JOIN wp_usermeta b ON a.user_id = b.user_id AND b.meta_key = 'first_name'
INNER JOIN wp_usermeta c ON a.user_id = c.user_id AND c.meta_key = 'last_name'
SET a.meta_value = CONCAT(b.meta_value, ' ', c.meta_value)
WHERE a.meta_key = 'nickname' AND a.meta_value IS NULL;

 

I just tried changing "IS NULL" to " = '' " and it worked perfectly!  Thank you very much for your help!