Prestashop Tip: Increase Prices with an SQL Query

Increase Product Prices on Prestashop with an SQL Query

Introduction

If you manage an online store with Prestashop, you may sometimes need to increase the prices of your products automatically. This operation can be tedious to perform manually, especially if you have a large number of products to modify. Fortunately, there is a simple and fast solution: an SQL query.

How Does It Work?

The SQL query will allow you to add a percentage to all the prices of the products in your store. It will also take into account the product's VAT to create a rounded inclusive price.

Here is the query:

#example 5% with rounded inclusive price based on a 5.5% VAT for products in a category list
UPDATE ps_product p
INNER JOIN ps_category_product cp ON p.id_product = cp.id_product
INNER JOIN ps_category c ON cp.id_category = c.id_category
SET p.price =(CEIL(price * 1.055 *1.05* 10) / 10) / 1.055
WHERE p.price IS NOT NULL AND c.id_category NOT IN (7, 62, 63);

#also update product_shop
UPDATE ps_product_shop p
INNER JOIN ps_category_product cp ON p.id_product = cp.id_product
INNER JOIN ps_category c ON cp.id_category = c.id_category
SET p.price =(CEIL(price * 1.055 *1.05* 10) / 10) / 1.055
WHERE p.price IS NOT NULL AND c.id_category NOT IN (7, 62, 63);

#example without rounding the inclusive price 
UPDATE ps_product p
INNER JOIN ps_category_product cp ON p.id_product = cp.id_product
INNER JOIN ps_category c ON cp.id_category = c.id_category
SET p.price =(CEIL(price *1.05* 10) / 10) 
WHERE p.price IS NOT NULL AND c.id_category NOT IN (7, 62, 63);

#also update product_shop
UPDATE ps_product_shop p
INNER JOIN ps_category_product cp ON p.id_product = cp.id_product
INNER JOIN ps_category c ON cp.id_category = c.id_category
SET p.price =(CEIL(price *1.05* 10) / 10) 
WHERE p.price IS NOT NULL AND c.id_category NOT IN (7, 62, 63);

#example with given supplier (rounded inclusive price 5.5% ) + 3%
UPDATE ps_product p
SET p.price =(CEIL(price * 1.055 *1.05* 10) / 10) / 1.055
WHERE p.price IS NOT NULL AND p.id_supplier IN (2, 36);

#also update product_shop
UPDATE ps_product_shop ps
INNER JOIN ps_product p ON p.id_product = ps.id_product
SET ps.price =(CEIL(ps.price * 1.055 *1.05* 10) / 10) / 1.055
WHERE ps.price IS NOT NULL AND p.id_supplier IN (2, 36);

These queries add 5% to your prices (*1.05), feel free to adjust with a little math!

And of course, even if the period is not suitable, you can adapt for a price reduction.

Conclusion

Using this SQL query, you can quickly increase the prices of your products on Prestashop. Don't forget to back up your database before running the query, in case you encounter any problems.

We hope this tip will help you save time managing your online store!