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

  1. Log into your cPanel.
  2. Once in the cPanel, find the Databases category and click on the phpMyAdmin icon.
    click phpmyadmin icon
  3. 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.
    select teh database to work with
  4. 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.
    click on the sql tab
  5. 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.
    enter the SQL query in the editor

    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
  6. 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%.
    BeforeAfter
    prices before the sql query prices after the sql query
Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve our Support Center:
Email Address
Optional, but our team may contact you for more information.
Like this Article?

Comments

Post a comment
2013-07-30 3:43 pm
same way is it possible to make reduction?
to show base price and final price beside it
Staff
10,626 Points
2013-07-30 3:56 pm
Hello Ilan316,

Thanks for the question. This article only shows how to make price changes using the SQL editor (in phpMyadmin) and does NOT show how to display the prices in any type of format. This is definitely possible, though it might require making changes to a specific template. There is also the option of finding an extension that does it for you.

Check out the following extension: Product Pricer. It's not a free extension, but if you view the sample site, I think that this is what you're looking for. Please consider this as a solution for the pricing option that you're requesting.

Let us know if you need any further assistance.

Regards,
Arnel C.

Post a Comment

Name:
Email Address:
Comment:
Are you a bot?
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!