3

We have several thousand products in our Magento store and every January we like to do a price increase of around 2-3%. In the past we have exported the products and manually edited the prices in the sheet and then uploaded the sheet. Is there a quicker way to do this?

3 Answers 3

6

Here is what I do. I don't say is the best method but it's the fastest. Always do a backup before trying it because this involves changing the db directly.
First get the id of the price attribute.

SELECT 
    attribute_id 
FROM 
    eav_attribute 
WHERE 
     attribute_code = 'price' AND 
     entity_type_id = (
          SELECT 
              entity_type_id 
          FROM 
              eav_entity_type 
          WHERE 
              entity_type_code = 'catalog_product'
      );

Let's say you get the value 99 from the select above.
Then I run this query:

UPDATE 
    catalog_product_entity_decimal 
SET 
    value = `value` * 1.02 
WHERE 
    attribute_id = 99 AND 
    entity_id in (1,2,3,5)

This will increase the prices with 2% for the specified product ids (in this example 1,2,3 and 5)

Reindex everything and you are done.

It doesn't get any faster than this, but also it hardly gets any riskier than this. Use with caution.

4
  • UPDATE catalog_product_entity_decimal SET value = 'value' * 1.02 makes all values = 0 and not a function of its original price
    – Jon
    Oct 10, 2017 at 11:28
  • The question was increasing prices in the whole store. Why did you answer for specific product IDs? Surely this is complicating the answer for no need?
    – Jon
    Oct 10, 2017 at 11:30
  • @Jon. If you take a look at the quotes around value, you will notice they are not quotes. Thats why you get 0. Also if you consider one additional filter as complicating it....
    – Marius
    Oct 10, 2017 at 12:40
  • Didn't know that character thanks. The question was why you complicated the answer. When I update the prices using catalog_product_entity_decimal the products on sale lose their sale prices. Do you know why?
    – Jon
    Oct 10, 2017 at 14:16
1

Unfortunately, native to Magento, there is not a way. Here are some modules that may be what you are looking for:

  1. http://www.magpleasure.com/mass-price-update.html
  2. http://amasty.com/mass-product-actions.html
  3. http://fishpig.co.uk/magento/extensions/update-prices/

Please note that I do not have personal experience with these and cannot vouch for the quality of their code.

1

Always grab a backup, and run on staging / development first, but a quick shell script to grab ALL products and increase their price. This doesn't disable indexing, so it may take a while. To speed things up, set indexing to manual, run script, and then reindex and set back to update on save.

ini_set('memory_limit', '8024M');

error_reporting(E_ALL);
require_once './app/Mage.php';

umask(0);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$collection = Mage::getResourceModel('catalog/product_collection');

foreach($collection as $product) {

    $_product = Mage::getModel('catalog/product')->load($product->getId());
    $price = ($_product->getPrice() * 1.02);
    $_product->setPrice($price);
    $_product->save();

}
2
  • 3
    load() inside a foreach is likely going to fall over with thousands of product records, no?
    – benmarks
    Feb 24, 2014 at 21:50
  • It can get quite resource hungry, however, it's not a full product object unless you load. If the collection product object returns price, you may get away without out it. The load is there in case you want to change more attributes at the same time. Feb 25, 2014 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.