We’ve worked with many clients who use Wordspress’ WooCommerce framework to run and manage their ecommerce stores, and quite often certain “adjustments” need to be applied to products on a bulk-scale.
Typically, there are many plugins available to service such needs – but sometimes, all you need is a quick once-off solution that gives you instant results, and there’s nothing quite like a SQL query to fulfill that need.
In a recent example of this, we’ve helped a client reset their tax rate on all their products using a SQL query. Usually this is easy to do, if products are published exclusive tax, with WooCommerce’s built-in tax rate feature – which allows you to add a specified percentage(%) on top of the given price of all products.
But in this particular case, the products had always been published inclusive tax; a given tax amount that was now 1% higher.
So how do you remove an inclusive tax amount (or any percentage amount for that matter) from all products in order to add back the updated tax amount in a dynamic and scalable way?
Divide the product amount by a given percentage
Since we are referring to tax rates in this case, the “given percentage” is the tax percentage.
The SQL query below will remove 14% from all products.
Note: the table prefix (wp_) may need to be changed as per requirements
SET `meta_value` = ROUND(`meta_value` / 1.14, 2)
WHERE meta_key LIKE ‘%_price%’
AND meta_value > 0
AND `post_id` IN (
SELECT `ID`
FROM `wp_posts`
WHERE `post_type` = ‘product’
AND `post_status` = ‘publish’
AND `ID` = `post_id`
);
Add a new tax amount
WooCommerce Tax Rate:
The best way to add tax to products is through a tax rate, the value of which can be changed as is needful and when required.
To do this in WooCommerce, follow these steps:
- Navigate to WooCommerce > Settings
- Select the “Tax” tab
- Select “Standard Rates” from the inline list of links
- Here you will see the “Standard” Tax Rates table, adjust an existing tax rate, or add a new one by inserting another row, then confirm your changes by clicking the “Save changes” button
- Once you have your tax rate set-up or configured, select “Tax Options” from the inline list of links
- Ensure that you have “Prices Entered With Tax” set to “No, I will enter prices exclusive of tax“
- Set the options “Display Prices in the Shop” and “Display Prices During Cart and Checkout” to “Including Tax“
Products will now display prices on the frontend with the tax rate defined included.
Alternatively, you could also add this value back through another SQL query.
Multiply the product price with a given percentage:
The disadvantages to this method is that you cannot keep your tax rates dynamic and scalable in this way.
But there are other cases where you may need to increase all products prices by a given value, whether it is a fixed or percentage value, that has nothing to do with tax.
The SQL query below will add 15% to all products
Note: the table prefix (wp_) may need to be changed as per requirements
SET `meta_value` = ROUND(`meta_value` * 1.15, 2)
WHERE meta_key LIKE ‘%_price%’
AND meta_value > 0
AND `post_id` IN (
SELECT `ID`
FROM `wp_posts`
WHERE `post_type` = ‘product’
AND `post_status` = ‘publish’
AND `ID` = `post_id`
);
In this article, we increased or decreased tax rates to demonstrate these queries, but these queries could be used to increase or decrease product prices in bulk by any amount for any reason.
