That old chestnut! Price updates are quick and easy to make if you only have a few items in your store, but what if you have hundreds or even thousands of products? The issue here is that updating pricing can become tedious and time consuming. When updating hundreds or even thousands of products, it’s inevitable that you’re more than likely to make a mistake!
What are the alternatives?
The first method – plugins.
We’re often asked to recommend plugins which will update prices in woocommerce. There are a few free plugins and a few paid for plugins and the choice is yours to make.
There are two issue here, the first is that a plugin either only updates single item prices and leaves variations unaltered, or vice-versa. The second, is the amount of resource used, which might affect your website’s performance.
Many free plugins simply aren’t up to the task at hand, particularly when you have a large store.
The second method, the woocommerce backend.
Of course you can do this in woocommerce, bulk items are a breeze to update, variations on the other hand are not. If you’re not sure how this works, then simply google ‘bulk price update woocommerce,’ and you’ll find many videos on the subject.
Our preferred method.
This isn’t the sort of thing that you can leave to a client, but if you’re familiar with the way in which the wordpress database works, it’s a simple and effective solution. You could also as we have, turn this into a plugin in which case just hand it over to your client.
This method provides a way to increase prices throughout the store, takes seconds and depending on what you want to do you can run each separately or amend the SQL queries so that only particular groups of products have price increases applied. We’ll touch on the SQL commands after examination of the process.
Remember, it’s your responsibility to ensure that you backup the database before you start to make changes!
Here are the necessary steps to follow:
- Login to phpmyadmin. This can be done via the browser address bar or via cPanel, so do so using your preferred method.
- Select the database that you want to work with.
- Ensure that your tables are prefixed with wp_ otherwise this method will not work!
- The database will look similar to this. The two tables that we’re interested in are wp_postmeta and wp_options.
- Now click on the SQL tab at the head of the table.
- Paste the required query(s) into this dialogue box.
The required SQL are as follow:
UPDATE wp_postmeta SET meta_value = ROUND(meta_value * 1.10, 2) WHERE meta_key = '_price'; UPDATE wp_postmeta SET meta_value = ROUND(meta_value * 1.10, 2) WHERE meta_key = '_regular_price'; UPDATE wp_postmeta SET meta_value = ROUND(meta_value * 1.10, 2) WHERE meta_key = '_sale_price' AND meta_value != ''; DELETE FROM `wp_options` WHERE (`option_name` LIKE '_transient_wc_var_prices_%' OR `option_name` LIKE '_transient_timeout_wc_var_prices_%');
Back to basics, to change prices replace 1.10 in line 2, 6 and 10 with your own values. In this case the value 1.10 adds 10% to all prices, so 1.15 will add 15%, you get the idea.
Now let’s look at what each set of commands do. The first 2 queries (lines 1 to 3) and (lines 5 to 7) change the price and regular price held in woocommerce. Lines 8 to 10 change the sale price, and there’s nothing to stop us using different values throughout, for example we could set the sale multiplier to 1.00 which would leave sale prices unchanged.
What’s not clear and is not intuitive is the purpose of the final 3 lines. These are in fact required to change product variation prices. If you run lines 1 – 7 of the code, what you end up with is a price increase for simple products. Product variations are only altered by adding the final 3 lines of SQL.