2

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.

0

1 Answer 1

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.

1
  • Perfect! Thank you for your help! I did change the set and the where around as I am wanting to push the address to the billing_address_1.
    – Erik
    Oct 16, 2015 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.