Update ‘meta_value’ from wp_usermeta table assigning value of another ‘meta_value’ of the same table

1 Views

I apologize for confusing and cumbersome question, but anyway I need to do following: In ‘wp_usermeta’ table I have fields (meta_keys) ‘category’ and ‘request_category’ for all users. Now I want overwrite meta_value of ‘category’ with meta_value ‘request_category’ where ‘category’ has a specific value.

But after executing SQL query I’m getting NULL in ‘meta_value’ fields of ‘category’. Here is my code:

Read More
UPDATE 
  wp_usermeta
SET
  wp_usermeta.meta_value = 
  (SELECT 
    umeta_tmp.meta_value 
  FROM
    umeta_tmp
  WHERE umeta_tmp.meta_key = 'request_category' 
    AND umeta_tmp.umeta_id = wp_usermeta.umeta_id) 
WHERE wp_usermeta.meta_key = 'category' 
  AND wp_usermeta.meta_value = 'travel_agency'

Where table ‘umeta_tmp’ is a temporary table where I kept data of ‘wp_usermeta’ table (since I can’t modify the same table which I use in the SELECT part).

Could anyone hint me how can I achieve that.
Thanks.

Post Views: 1

Related posts

1 comment

  1. Convert into UPDATE with JOIN

    UPDATE wp_usermeta
JOIN umeta_tmp ON umeta_tmp.meta_key = 'request_category' 
               AND umeta_tmp.umeta_id = wp_usermeta.umeta_id
SET wp_usermeta.meta_value = umeta_tmp.meta_value
WHERE wp_usermeta.meta_key = 'category' AND 
      wp_usermeta.meta_value = 'travel_agency'`

    If the umeta_tmp table is just the copy from the wp_usermeta table then the JOIN and WHERE conditions are contradictious because you want the meta_key field of the same row (equalization by id) to be 'request_category' in the umeta_tmp table and at the same time to be 'category' in the wp_usermeta.
    Having this in view and moving the WHERE condition into the JOIN the result will be the following:

    UPDATE wp_usermeta
JOIN umeta_tmp ON wp_usermeta.meta_key = 'category' AND 
               wp_usermeta.meta_value = 'travel_agency' AND
               umeta_tmp.umeta_id = wp_usermeta.umeta_id
SET wp_usermeta.meta_value = umeta_tmp.meta_value

Comments are closed.