InMotion Hosting Support Center

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
Continued Education in Course Prestashop 1.5
You are viewing Section 27: 340: Making a mass price change for PrestaShop using phpMyAdmin
Section 26: 341: How to find your database name for PrestaShop 1.5
Section 28: 339: How to remove footer information in PrestaShop 1.5

Support Center Login

Social Media Login

   
Social Login Joomla

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
2013-07-30 3:43 pm
same way is it possible to make reduction?
to show base price and final price beside it
Staff
26,137 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.
n/a Points
2014-05-24 7:36 am

Superb! Saved lot of time for me.

 

Exactly what i was looking yo change.

 

 

n/a Points
2014-08-16 7:41 pm

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

Staff
28,948 Points
2014-08-18 11:35 am
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_shop SET price = price*1.20 WHERE id_category_default=3
UPDATE ps_product SET price = price*1.20 WHERE id_category_default=3


Kindest Regards,
Scott M

n/a Points
2014-08-31 7:57 am

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?

Staff
28,948 Points
2014-09-02 12:53 pm
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
n/a Points
2014-09-12 12:02 pm

Is it possible to change the prices to '0'... I mean:

UPDATE ps_product_shop SET price = price*0UPDATE 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

Staff
28,948 Points
2014-09-12 12:19 pm
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.
UPDATE ps_product_shop
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
n/a Points
2015-02-13 2:03 am

Exactly what i was looking for. Thank you. I get a lot of help from your blog.

n/a Points
2015-03-19 7:19 pm

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 

http://brixton-beds.co.uk/beds-mattresses/1-yardley-1000-pocket-spring-divan-bed.html

http://brixton-beds.co.uk/bedroom-furniture/6-budget-four-drawer-chest.html

http://brixton-beds.co.uk/beds-mattresses/308-panama-pine-wood-bed.html

Staff
28,948 Points
2015-03-23 1:09 pm
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
n/a Points
2015-04-01 8:00 am

Hi, Thanks for this tutorial :) 

I have seen this http://addons.prestashop.com/en/bulk-update-prestashop-modules/18895-bulk-price-update.html which changes product prices in bulk. 

n/a Points
2015-04-21 1:38 am

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: http://www.fmemodules.com/en/73-bulk-price-update.html. The extension helps store owners to add or subtract prices of product at once.  

n/a Points
2015-06-14 12:28 pm

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!

Staff
28,948 Points
2015-06-15 3:40 pm
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

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

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

18 Questions & Comments

Post a comment

Back to first comment | top

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!