Modify prices by percentage in opencart

Posted by Flo on 21 Jun 2014

There are lots of extensions on the opencart extension directory to bulk modify product prices. However if you only have to do this once in a while or you are looking for a quick free way to do this, you could do it by running a sql query directly in your database in phpmyadmin.

So first youll have to log into your cpanel and access phpmyadmin and in phpmyamin access the database your store uses(the databases are listed in the left column). Now that you are in your store's database select the sql tab and there you will run the code below (after you apply it your modifications).

To increase all prices by 10% the code would be like:

UPDATE preffix_product SET price = price*1.1;

In the above code preffix_ is meant to be replaced with the preffix you have on your database tables or if you dont have one then write no preffix and the line would be like:

UPDATE product SET price = price*1.1;

If you wanted to decrease all prices by 10 percent , the code would be like this:

UPDATE preffix_product SET price = price*0.9;

Or if you would want to get back to the original products prices after a price increase of 10% you would run:

UPDATE preffix_product SET price = price/1.1;

You could also modify the prices of all products from a certain manufacturer, you would only need to know the manufacurer_id of the manfuacturer you are targeting (You can see the manufacturer_id in the url when you edit a manufacturer in admin).

So if you would want to increase by 19.5% the prices of the products from manufacturer X that has the manufacturer_id 9, the query would be like:

UPDATE preffix_product SET price = price/1.195 WHERE manucaturer_id = "9";

If you are not very familiar with phpmyadmin you could be running these queries straight in the admin of opencart: Create a file import.sql (it must have the sql extension), in it add the query you wish to run and save it. Then in opencart's admin go to system > backup/restore and import the sql file you just created.

Dont forget about the preffix your tables use and also make a database backup before you run the queries.