Office: 0194 386 4688 | Bob: 0740 332 1862

Woocommerce bulk price increase

7 February 2019

Woocommerce bulk price increase

That old chestnut, I hear you say. Price updates are simple if you only have a few items in your store, but what if you have hundreds or even thousands? The issue here is that updating pricing can become really tedious and time consuming. Then there’s the problem of spotting mistakes.

So what precisely 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. The problem with many such plugins is that they either update single item prices and leave variations alone, or vice-versa. Many of the free plugins simply aren’t up to the task at hand, particularly when you have a large store.

The second method – 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 elegant solution. You could also as I 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.

Important – remember, it’s your responsibility to ensure that you backup your database before making any 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 you 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.

MySQL database 

Phpmyadmin database login

  • Now click on the SQL tab at the head of the table.

Open SQL Dialogue

  • Paste the required query(s) into this dialogue box.

SQL commands
Database change results

SQL commands.

The required SQL are as follow:

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.