While maintaining your PrestaShop store, at times you may need or want to update the prices for all products at once. Perhaps you are having a sale where you allow 20% off all products, or you may simply need to increase your prices due to an increase in supplies. Whatever the case, this can be a tedious and time consuming process if you have more than a handful of products. If you have thousands, this can seem downright impossible. This can be done, however, with a simple SQL query. This is not done in the PrestaShop back office, but using a program such as phpMyAdmin. Follow along below as we take you through making a mass price change in your PrestaShop database using the phpMyAdmin tool.
How to make a bulk price adjustment using SQL in phpMyAdmin
- Log into your cPanel.
- Once in the cPanel, find the Databases category and click on the phpMyAdmin icon.
- This brings you to the main phpMyAdmin page. Look to the left hand sidebar and find the name of your PrestaShop database. Click on the name to begin working with it. Our database is named pshop15. If you do not know your database name, you can learn how to find it here.
- After clicking on the database name, the right hand panel will populate with a list of tables. More importantly, the work tabs at the top appear. Click on the SQL tab to open the SQL query editor.
- From the SQL query editor, you will need to enter the code to make the price change. Below are a few sample codes to demonstrate increasing and decreasing prices by both specific amounts and percentages.
Sample code to increase all prices by twenty percent (20%).
UPDATE ps_product_shop SET price = price*1.20
UPDATE ps_product SET price = price*1.20
Sample code to decrease all prices by 15 cents (.25).UPDATE ps_product_shop SET price = price-.15
UPDATE ps_product SET price = price-.15
Sample code to increase all prices by fifty cents (.15).UPDATE ps_product_shop SET price = price+0.50
UPDATE ps_product SET price = price+0.50
Sample code to decrease all prices by ten percent (10%)UPDATE ps_product_shop SET price = price / 1.1
UPDATE ps_product SET price = price / 1.1 - Once you have entered a query with your desired price change, click the Go button at the bottom. This will activate the query and make the change. Your prices should now display with the new changes. Below is a before and after example of a price change where we increased the prices by 10%.
Before After 
Hey guys,
thanks for this article! This is what I tried:
UPDATE ps_product_shop SET price = price+0.30 WHERE id_category_default = 13
UPDATE ps_product SET price = price+0.30 WHERE id_category_default = 13
But it is not working as phpmyadmin shows me the error #1064
It is an error in the SQL Syntax because of the word ‘UPDATE’
Any advice? Thanks so much in advance!
I recommend reviewing the official MySQL guide on the Update Statement, it includes examples of using the syntax.
When I update the combination prices in the database in phpMyadmin the prices does not affect in Prestashop?
I found an older post in the PrestaShop forums where they mention that the combination prices are stored in the price impact section of the ps_product_attribute_shop table.
Thanks!!!
That solved the problem 🙂
Hi,
can you also tell how to add a special price on all iteams ? let say 10% less for weekend 11-13 May 2018
Daria
Here is a helpful link to our guide on Setting a product On Sale in PrestaShop.
Thank you,
John-Paul
Hello how could I apply product attribute combination to all my products?
Prestashop’s documentation states:
“Once your attributes are in place and their values are set, you can create product variations (or combinations) in each product’s Combinations tab, from the Products page under the Catalog menu.”
I would check there to see if there is a “bulk” method to set multiple products at once. I hope this helps!
I have a few more to add.
Decrease the price 15% for a range of products 2 – 9
UPDATE ps_product SET price = price*0.85 WHERE id_product > 1 and id_product < 10;UPDATE ps_product_shop SET price = price*0.85 WHERE id_product > 1 and id_product < 10Increase the price 10% for all products in Category ID 1
UPDATE ps_product_shop SET price = price*1.10 WHERE id_category_default = 1UPDATE ps_product SET price = price*1.10 WHERE id_category_default = 1Thanks for contributing to the Community! We appreciate your participation and hope that others can benefit from the information you have provided!
Hello.
I would like to update quantity, price and price discount from a csv file through php my admin.
Is this possible?
Thanks in advance.
Unfortunately, this is not possible via standard phpMyAdmin functionality. However, in reviewing your inquiry online, I was able to find a guide on “Using CSV Files to Update Database Tables with phpMyAdmin” that details a work around that should help you.
Hi,
Very interesting and helpful article, but what if we would need to adjust the prices based on the id_category of table ps_category_product ?
Thanks in advance
That would need a different, slightly more complicated query. At this time we do not have one written for that.
Hi! What if i would like to do a mass price change of a selected manufacturer. (A field id_manufacturer is in ps_product, but not in ps_product_shop). Maybe someone has an idea? Thanks
Hello mmr,
We have not yet tested a query for individual manufcturers so we currently do not have one for you.
Kindest Regards,
Scott M
Hi again,
Here is the answer to my question :
UPDATE ps_stock_available SET quantity=12
Thank you again for your post.
Great tip ! Thank you !
May I ask you the exact sql command in order to change all the products quantities ?
Thank you again !
SCOTT – thank you for your previous response. Your category mass price update worked like a charm and I’ve used it several times without fail. I have another “bulk” modification I would like to do and would like another SQL trick if available.
Lets say I have a Category of products with 20 subcategories beneath it. I would like to be able to change the “Available to Order” status from ‘not available’ to ‘available for order’. Keep in mind they are an active item, they are simply not available to order. THe simple function of “enable all” in the PS Admin will not work, and there are several hundreds to make ‘available to order’. I know that I could go through and first delete all of the products – then reupload all of the products using a CSV import with a ‘1’ instead of a ‘0’ in the ‘Available to Order’ field, but would rather not.
Any suggestions?
THanks!
Hello Andre,
That won’t be quite as simple as the ones above, but if you know a little about SQL it sounds doable. You would need to be pretty familiar with the database however to ensure that change did not affect any related tables. My advice would be to test the query and build it little by little on a copy of the database.
Kindest Regards,
Scott M
Thanks inmotionhosting team your tutorial really helps users to understand and implement on their website to easily get such customization. If you want to change prices of your multiple products with just one click then I would recommend you to use PrestaShop bulk price module by FME here: https://www.fmemodules.com/en/73-bulk-price-update.html. The extension helps store owners to add or subtract prices of product at once.
Hi, Thanks for this tutorial 🙂
I have seen this https://addons.prestashop.com/en/bulk-update-prestashop-modules/18895-bulk-price-update.html which changes product prices in bulk.
I have almost 400 products in my store , each product has its own discount, for example if a Bed had £50 discount and Dining table has £ 90 Discount, but I don’t know why each product is showing the discount of £ 20 and i have tried to change it from back end by editing , deleting the old discount and adding the new one. but still on from page each product is showing £ 20 discount and if I change price to £ 0 , still £ 20 discount is showing. How to get back my old Discounts on each product?
Here are the Links for my products
https://brixton-beds.co.uk/beds-mattresses/1-yardley-1000-pocket-spring-divan-bed.html
https://brixton-beds.co.uk/bedroom-furniture/6-budget-four-drawer-chest.html
https://brixton-beds.co.uk/beds-mattresses/308-panama-pine-wood-bed.html
Hello Brixton,
What are the exact steps you are taking to enter a discount for a product? Please be as specific as possible so we may try and duplicate the issue on our test site.
Kindest Regards,
Scott M
You missed two tables if you have variations on your products:
Hello Julien,
I appreciate your input, we will investigate that and most likely add it to the article. Again thanks for your input.
Best Regards,
TJ Edens
Exactly what i was looking for. Thank you. I get a lot of help from your blog.
Is it possible to change the prices to ‘0’… I mean:
UPDATE ps_product_shop SET price = price*0
UPDATE ps_product SET price = price*0
I get this error on phpmyadmin…
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UPDATE ps_product SET price = price*0’ at line 2
Hello Rainer,
You need a semicolon ‘;’ after the first update statement to separate the two. This will then wipe all prices in the ‘price’ column for the two tables. You may want to modify the code to also include the ‘wholesale_price’ column.
SET price = 0, wholesale_price = 0;
UPDATE ps_product
SET price = 0, wholesale_price = 0;
Note I simply set the prices to 0 instead of using column*0 since it was a hardcoded price. Either will work.
Kindest Regards,
Scott M
Mass price changes successfully done, but the price in the display/catalogue remains the same. But when it moves to the next page the price changes show. Please advise?
Hello Joe,
That should have taken care of the price display as well. To my knowledge it covers all areas of the database that has the prices. You may need to clear either your browser cache or disable the smarty cache in the PrestaShop back end.
Kindest Regards,
Scott M
But what about a situation where you want to increase the prices in only one category of products on a website that has dozens of categories? Eg:
Raise all prices in category: American Widgets
Leave the prices the same in: Candian Widgets, Mexico Widgets, British Widgets
Hello Andre,
You would follow the same procedure, but the SQL query would be a bit more complex. You would first need to find out the specific category ID for your desired category. Using the sample data in a fresh PrestaShop installation, I see under the ps_category_lang table that the ipod category has an id_category value of 3. This matches the id_category_default column for the ps_product table. (Note that if a product is in more than one category, this method only changes the price if it has your desired category set as its default.)
You would then alter the UPDATE statement above to include the id_category value. For example, to update all prices for products with the ipods category (value of 3) as the default:
UPDATE ps_product SET price = price*1.20 WHERE id_category_default=3
Kindest Regards,
Scott M
Superb! Saved lot of time for me.
Exactly what i was looking yo change.