Update prices WooCommerce
How to update all prices in about 50 WooCommerce sites, with each about 15.000 products, preferably all on the same day?
Exact calculations
- Increase prices with 15%
- Round off to above to have a figure ending in .95 (or the equivalent in currencies with different numbers of zeros).
Example:
- Price (incl. VAT): € 9.95
- 15%: € 1,4925
- New price: € 11,4425
- Roud off to ,95: € 11,95
WP-CLI?
The royal approach would be through WP-CLI. Some thoughts:
- With
wp wc product list
, I could retrieve all IDs - Subsequently, read-out prices, update prices, and write them back with
wp wc product update
Problems:
- How to loop through all IDs, read & write a price? I usually use chaining, and this would probably something new. Store the id's in an array and than loop through that?
- This approach is probably very slow. Might takes days, maybe even weeks. That makes it very error prone.
SQL?
Quick and dirty: Do it through SQL. I could do all 60 sites in probably less than a minute. The basic SQL code isn't complicated either (source):
update wp_postmeta set meta_value = replace(meta_value, ",", ".") where meta_key="_price" or meta_key="_regular_price";
How about VAT?
- Prices have been entered including VAT
- WooCommerce will assume that this is against the relevant tariff of the home country of the shop (Netherlands in this case)
- Unless a visitor places an order, he/she will she the prices incl. VAT as they are in the Netherlands.
All details have been wonderfully figured out here: WooCommerce & BTW
Solution
I have a Bash library for doing this kind of stuff rather automatically. Part of it is a routine (load-site-array.sh
) to assign names of all sites and databases into an array. Subsequently, a routine would loop through this array and do stuff. However, this price update is quite a one-off event: Not something I would repeat every couple of months, probably not more frequent than every couple of years. Hence it would have been overkill to do this in such a flexible way: Program today for today and tomorrow for tomorrow.
In the end, I used plain SQL with names of databases hardcoded - Much more appropriate!
An impression of this script:
# # Update prices on various servers ################################################################################ # # * Just hardcoded in SQL - That's so much easier than through elegant Bash # code! # # ################################################################################ # nl_en ################################################################################ # # use example_nl; # # Orientation (1) ######################################## # # select distinct # meta_value as price_old, # 0.15*replace(meta_value,",",".") as price_increase, # 1.15*replace(meta_value,",",".") as price_increased, # floor(1.15*replace(meta_value,",","."))+.95 as price_new # # 1.15*meta_value as price_increased # from # wp_postmeta # where # meta_key="_price" # or # meta_key="_regular_price"; # # Commit # ######################################## # # # update # wp_postmeta # set # meta_value = floor(1.15*replace(meta_value,",","."))+.95 # where # meta_key="_price" # or # meta_key="_regular_price"; # # Orientation (2) # ######################################## # # # select distinct # floor(1.15*replace(meta_value,",","."))+.95 as price_new # from # wp_postmeta # where # meta_key="_price" # or # meta_key="_regular_price"; ################################################################################ # Etc. ################################################################################ # # use example_be; # update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; # use example_com_es; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; ################################################################################ # OK - au_en ################################################################################ # # use example_australia_com; # select distinct # meta_value as price_old, # 0.15*replace(meta_value,",",".") as price_increase, # 1.15*replace(meta_value,",",".") as price_increased, # floor(1.15*replace(meta_value,",","."))+.95 as price_new # # 1.15*meta_value as price_increased # from # wp_postmeta # where # meta_key="_price" # or # meta_key="_regular_price"; ################################################################################ # Etc. ################################################################################ # # use example_canada_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; # use example_ch; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; # use example_cn; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; # use example_co_uk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price"; ################################################################################ # OK - cz_en ################################################################################ # # # Orientation ######################################## # # use example_cz; # select distinct # meta_value as price_old, # 1.15*replace(meta_value,",",".") as price_increased, # (floor(1.15*replace(meta_value,",",".")/100)+.95)*100 as price_increased # from # wp_postmeta # where # ( # meta_key="_price" # or # meta_key="_regular_price" # ) # and # meta_value<>""; # Production ######################################## # # use example_cz; # update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; ################################################################################ # Etc. ################################################################################ # # use example_dk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_fr; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_in; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_ireland_com; # update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_it; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_jp; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_lu; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_nigeria_com; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_nz; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_pl; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_portugal_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_se; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_shop_at; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use exampleshop_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_shop_de; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_sk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_turkey_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_ukraine_com; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use example_usa_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>""; # use beispiel_webshop_de; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
Note:
- It updated sites on multiple servers. How this was done: I used multiple profiles in MySQL Workbench and every time loaded the same script
- Turned out that there were basically two kinds of updates: For sites with a currency that use two decimals (make prices end at .95), and for currencies that don't use decimals (make prices end at 95) - That was another thing I learned from doing this manually. It will make this easier next time
- Execution per site was about .5 to .7 seconds. I didn't compare this to WP-CLI, but I suspect that would be hours per site.