Bulk Price Update

In Category OpenCart Modifications
Article published 15/10/2012 10:20:34am
Bulk Price Update

Sometimes, it may become necessary to bulk-update your product prices in Open Cart. You might find that your supplier raises all prices by a specified percentage, for example. Updating all product prices manually can be impractical when you've got hundreds or even thousands of products, but thankfully it can be achieved quickly and painlessly through MySQL.

We're going to be assuming you have a basic knowledge of using cPanel, or whichever interface your host uses, to run MySQL queries.

Get yourself ready to run a command on your opencart database (in cPanel, for example, you would goto phpMyAdmin -> select your opencart database -> click on the "SQL" tab on the right frame).The command itself is ever-so-simple; we'll be increasing all prices by 15%:

UPDATE `product` SET price=round((price+((price/100)*15)), 2);

We've made that 15 prominent as this is the percentage we're using.

Just change this number for the percentage you'll be modifying the price by.That's it! Painless! But now let's look at a slightly more complex scenario...

Updating all prices in a category in OpenCart

You may find yourself in a situation where you only want to update the prices of products in a specific category.

Thankfully, again, this is both achievable and pretty easy with a simple bit of MySQL.First, we have to find the category ID.

We can do this by viewing the category from the admin section Catalog -> Categories -> (desired category) -> edit. The URL (page address) in your browsers address bar should end with category_id=[number], and it's the [number] that we're looking for.

Finding OpenCart Category ID

In our case, the category ID is 3815, and we'll once again be increasing prices by 15%:

UPDATE `product` AS t1 JOIN `product_to_category` AS t2
ON t1.product_id=t2.product_id
SET t1.price=round((t1.price+((t1.price/100)*15)), 2)
WHERE t2.category_id=3815;

The red 15 is the percentage to modify your prices by, and the blue 3815 is your category ID; simple! Now all prices of products in this category have been updated! (You can enter this all as one line - we just added the line-breaks for read-ability!).

Finally, one more quick solution we'll be looking at...

Bulk Modifying OpenCart prices by Manufacturer

This time, we're only going to update prices from a specific manufacturer. We need the manufacturer ID, which we can also obtain from the stores Admin section by going to catalog -> Manufacturers -> (desired manufacturer) -> edit and looking at the page URL:

Finding Opencart Manufacturer ID

In our case, the Manufacturer ID is 26. Our MySQL command is now pretty simple:

UPDATE `product` SET price=round((price+((price/100)*15)), 2)
WHERE manufacturer_id=26;

Once again, we've modified the price by 15% (red), and we've done so for all products with the manufacturer_id 26 (green).Thankfully, knowing these simple MySQL queries can mean the difference between spending just a minute or several days updating prices on your shop.

Sometimes, a little MySQL goes a long way...