One of the problems that comes of having a large inventory is making changes on a large scale. Unfortunately, this is not easily done within the default interface provided by Prestashop. For example, if you need to change all of your inventory pricing by 10%, then you would have to change each item individually. If your inventory items numbered in the hundreds or thousands, then this could quickly become too tedious a task without some type of automation. Fortunately, this can be accomplished using a SQL query. In order to make the query, you will need to have access to the MySQL database using a database client like phpMyadmin (available in the Cpanel interface). The following article will walk you through the process of creating a SQL query to update your entire inventory in the PrestaShop database.
Creating the SQL Query for a Mass Price Update
- Login to the Cpanel
- Scroll down the Cpanel interface until you find the DATABASES section. Click on phpMyAdmin to continue.
- When the phpMyAdmin interface opens, the list of database will be in a column on the left (as per the screenshot at right). Scroll through this list of databases until you find the database associated with your PrestaShop 1.5 installation. If you need help finding your database name, go to Locating the Database Name Prestashop 1.5 store.
- Click on your database once you find it. This will change the screen view to show the tables that make up the structure of the Prestashop database.
- At the top of the phpMyAdmin interface you will see the tab labeled SQL. Click on this tab in order to build the query.
- The following listed code samples provide you the code that you can enter in order to make your bulk price changes:
To Decrease the price:
Decease by a fixed amount of currency (code example decreases all prices by 25 cents):
UPDATE ps_product_shop SET price = price-.25
UPDATE ps_product SET price = price-.25
Decrease by a percentage (code example decreases all prices by 25%)
UPDATE ps_product_shop SET price = price / 1.25
UPDATE ps_product SET price = price / 1.25
To Increase the price:
Increase by a fixed amount of currency (code example increases prices by $1.00)
UPDATE ps_product_shop SET price = price+1.00
UPDATE ps_product SET price = price+1.00
Increase by a percentage (code example increases prices by 10%)
UPDATE ps_product_shop SET price = price*1.10
Select the code that best meets your needs and enter this code into the SQL query window that you opened in Step 5.
UPDATE ps_product SET price = price*1.10
- After you have finished entering the code, click on the GO button in the bottom right of the screen. Note that this will change the prices for all of your products.
In order to see the effect on the prices check out the screenshots below. The screenshots show the increase in price by $1.00: