How do I copy from one meta_key to another meta_key in same table?

Question

I am trying to copy the address to the billing address. Both are located in the wp_usermeta table^†. I picture something that looks similar to this:

update wp_thafxavhs9_usermeta(my table) 
set billing_address_1(meta_key want copied to) = address(meta_key want copied from)

but I know I'm missing something because they are meta_keys. Also, I am trying to do this for all users and I'm using phpMyadmin

Do I need to do anything special to have it copy for all users? But yeah, that's all I'm trying to do. WooCommerce pulls from the billing_address field to populate the forms, so I figured it would be easy since I already have the address.

When I tried:

update 'wp_thafxavhs9_usermeta'
set billing_address_1 = address

I got the following error message:

1054 - Unknown column 'billing_address_1' in 'field list'

Clearly the error message says the field billing_address_1 isn't there. (This I just copied from the database, unless I typed something wrong in my sql...)

I have updated my registration form that new members fill out when joining the site, so it captures all that for new members.

Here is the table schema:

SHOW COLUMNS FROM wp_thafxavhs9_usermeta;

Field        Type               Null        Key     Default     Extra
umeta_id    bigint(20) unsigned NO          PRI     NULL        auto_increment
user_id     bigint(20) unsigned NO          MUL     0    
meta_key    varchar(255)        YES         MUL     NULL     
meta_value  longtext            YES                 NULL     

---

†: My table names somehow magically changed when we switched over from some crappy shared hosting to wordpress hosting. Not sure how that all happened, but it works.

Answer 1

Based on the table structure what you need is a self-join in your update. To find lots of examples type mysql self join update into your favorite search engine. I think the basic structure you're looking for is:

UPDATE 'wp_thafxavhs9_usermeta' AS 'billing'
INNER JOIN 'wp_thafxavhs9_usermeta' AS 'address' ON 
   'billing'.user_id = 'address'.user_id AND 'address'.meta_key = 'address'
SET
   meta_value = 'address'.meta_value
WHERE
   'billing'.meta_key = 'billing_address_1'

Of course don't run that or any SQL you find on the interwebs on production without testing it in a safe environment first. That might not give you exactly what you want but should give you a solid start.

Also you should keep in mind that this won't keep the columns synced as new users are added. In order to do that you'll need to change your insert/update logic or add a trigger. Personally, I would recommend changing your insert logic over the trigger, but that isn't always practical.