Magento: Update Product Prices Globally

There are many ways to mass update product attributes in Magento, each well suited to a different purpose. Magento's built-in mass product attribute updater is great if you want to modify a selection of products or the new attribute value is the same for all products you want to edit. Alternatively, if you wish to alter the attributes in more dynamic ways, updating them programmatic ally via PHP is probably a better way. (The best way would be using our new Magento Price Changer extension!). The downside to both of these methods is speed, with each product update taking a few seconds to complete. While this time can be dramatically reduced by disabling indexing, the wait can still be too long for a store with a massive catalog. A more efficient way to update product attributes is to write direct SQL queries. As an example, I will show you how to mass update product pricing for all products, products from a certain store and products that use a certain attribute set.

Why would I want to mass update price?

When I was first asked to do this I asked myself the same question, however, the reason is quite simple. In Magento, shipping costs aren't usually displayed to the user until they enter their delivery address. While this makes sense, the customer usually enters their delivery address during the checkout process, meaning a lot of customers weren't aware of this extra cost. During a study of one site, I found that almost 30% of customers were leaving the store during checkout and that this bounce rate could almost definitely be attributes to the shipping cost. To remove this problem, it was decided I should add £6 (the shipping cost) on to every product price and offer free shipping instead. As soon as this was done a lot less people left the site during checkout!

How do I update product price globally?

In this first example, I will add £6 to every single product price.

<?php

$priceToAdd = 6;

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
  UPDATE catalog_product_entity_decimal val
  SET  val.value = (val.value + $priceToAdd)
  WHERE  val.attribute_id = (
     SELECT attribute_id FROM eav_attribute eav
     WHERE eav.entity_type_id = 4 
       AND eav.attribute_code = 'price'
    )
");

If you have a development site, add the code to a template file or run Magento's code in an external PHP file and all of your products should now cost £6 more.

How do I update all prices from a certain store?

This technique is useful when working in a multi-store Magento environment. The SQL query used is very similar, except you will need to add a clause in the WHERE section to limit the records updated by store ID.

<?php

$priceToAdd = 6;
$storeId = 4;

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
  UPDATE catalog_product_entity_decimal val
  SET  val.value = (val.value + $priceToAdd)
  WHERE  val.attribute_id = (
     SELECT attribute_id FROM eav_attribute eav
     WHERE eav.entity_type_id = 4 
       AND eav.attribute_code = 'price'
    )
    AND val.store_id = $storeId
");

How do I update all product prices with a certain attribute set?

The concept behind this is the same, however you will need to join an extra table so that you can filter using attribute_set_id.

<?php

$priceToAdd = 6;
$attributeSetId = 4;

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
  UPDATE catalog_product_entity_decimal val
  SET  val.value = (val.value + $priceToAdd)
  WHERE  val.attribute_id = (
     SELECT attribute_id FROM eav_attribute eav
     WHERE eav.entity_type_id = 4 
       AND eav.attribute_code = 'price'
    )
AND entity_id = ( 
   SELECT p.entity_id FROM catalog_product_entity p
   WHERE p.attribute_set_id = $attributeSetId
)
");

How do I update the Special Price?

This one is also extremely easy! If you take any of the above examples and swap 'price' for 'special_price' they will all work! See below for an example of how to update the special price for every product.

<?php

$priceToAdd = 6;

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
  UPDATE catalog_product_entity_decimal val
  SET  val.value = (val.value + $priceToAdd)
  WHERE  val.attribute_id = (
     SELECT attribute_id FROM eav_attribute eav
     WHERE eav.entity_type_id = 4 
       AND eav.attribute_code = 'special_price'
    )
");

These features only scratch the surface of the Magento database but should hopefully give you an insight into the possibility of modifying data directly in the database. This method is much quicker than the alternatives, however can go drastically wrong extremely easily. I would make sure you test ALL queries on a development server and always back up your live server before running a query!

For more information on the Magento database scheme please see the article: Magento Database: EAV.

26 thoughts on “Magento: Update Product Prices Globally”

  • Coolshop

    This is a great post. There aren’t too many post that deals with Magento SQl and EVA database.

    How would you go about updating the prices for all products in a given category?

  • BT

    Hi Coolshop,

    You’re right, there aren’t too many article’s about Magento EAV. It’s weird though because the EAV database is a massive part of Magento.

    I wrote an article on EAV in Magento, which you might find useful:

    http://fishpig.co.uk/2010/06/07/magento-database-structure-eav/

    It would be possible to write a direct SQL query to only affect products in a certain category using something similar to the following:
    // Where the category ID is 4
    (category_ids LIKE (’4′) OR category_ids LIKE (’4,%’) OR category_ids LIKE (‘%,4′) OR category_ids LIKE (‘%,4,%’))

    As the category ID’s are comma separated, the above query checks the potential position of the ID relative to the start, end and commas.

    An easier way would be to write a Magento script that outputs a serialised array of all product ID’s for a certain category. You could then use:

    “entity.id IN (” . implode(‘,’, $productIds) > “)”;

    I hope this helps!

  • way2real

    Hi Fishpig. I am trying to globally update Special Price and Special Price From Date of a certain category. Can this be done in a similar manner? Any hints?

    Thanks for the great write up.

  • BT

    Updating the special price is extremely simple! In the above examples, the price attribute is loaded in dynamically by the following code:

    eav.attribute_code = ‘price’

    If you change that to the following, the special price will be updated.

    eav.attribute_code = ‘special_price’

    It’s that simple!

  • erwik

    there are no means to use some helper or model directly in magento core?
    I am not sur use the database was such a good idea...

  • BT

    @erwik

    I'm not entirely sure what you mean. If you want to access helpers and models just use:

    $helper = Mage::helper('catalog/category');
    $category = Mage::getModel('catalog/category');

    If you're running the SQL without the Magento engine, try looking at the following article which explains how to access Magento functions in pure PHP code.

    http://fishpig.co.uk/2010/04/12/run-magento-code-externally/

  • Roshan

    Hi I am Roshan and thank you for all your useful articles. I am working on magento for four months. I need you help.
    I am making a book store and it has a feature for gold imprinting cover. So whenever user add some letter in gold imprinting attribute. A total $5 should be added into total.

    Please help me how can I do this.

    Regards

    Roshan

    • BT

      Hi Roshan,

      I have a way you could achieve this functionality quite easily but it requires a long explanation so I will send it to your email.

      Thanks for checking out my site and I'm glad you like it!

  • Roshan

    Thank you Ben for sharing your knowledge.

  • mics

    Hi There,

    Thanks for all your usefull information.

    I have a need to write a script to update prices on our database, we use a mixture of configurable prods, simple prods and grouped prods.

    A)We get price changes every month and need to be able to update our database based on these changes. It could be all items for a particular supplier. Our master file external to Magento controls the prices. When we get an update we need a mechanism for updating the mag tables,

    B)Second is I want to update all our product prices using a file with the SKU and the Price. Have u a solution that would easily work on 5000 skus?

    Thanks a lot

    Kevin

    • BT

      Hi Kevin,

      I produced something very similar for a local company recently. The Magento module crawled a manufacturer website and pulled the information into Magento. This had both simple and configurable products in.

      The module I wrote worked for way more than 5000 SKU's, however, it did take a while to run. To increase speed, as you're only updating prices (my module added whole product records) it would be easier and quicker to update the prices using direct SQL queries.

  • coolmind

    hello fishpig..i need to update the price of all products under a category..The category id is given..i got some idea from your tutorial on eav..but i'm very week in sql.. please give me the exact sql code to select product price & product name under a category for example category id=4.I will be greatful to you thanx in advance...

    • BT

      @coolmind

      If you are very weak with SQL then I would not recommend executing direct SQL queries on your Magento database. If you insist on this, I would recommend backing up and performing any queries on a development site first. To limit the products affected by the price to products from a certain category, you would need to use the table called 'catalog_category_products'. This stores category/product links and should help you achieve what you need.

  • chaz

    Hi fishpig,

    I'm trying to do an update for different price for size of configurable products. In backend we can define different price for each size using the Super product attributes configuration where we can set the price by fixed or percentage. I realized that the involved table is in catalog_product_super_attribute_pricing
    How can i create/update pricing_value in catalog_product_super_attribute_pricing table via php script?

  • R Hopman

    Hello,

    I would like to use this. But how it's possible to increase all prices with 5%?

    Thanks for helping me out!

  • Paul

    Hi, am running 1.4.1.1. We're using a direct sql to modify the magento database in our store. For some reason the category view lists on front end wont update when prices are changed even iwth a manual reindex done afterwards. Any Ideas? The sql we're using is shown below.
    Its as if on or more tables which need to be updated are being missed out. Thanks in advance. Paul

    "UPDATE catalog_product_entity_decimal, catalog_product_entity
    SET catalog_product_entity_decimal.value = ROUND((catalog_product_entity_decimal.value * 1.2),2)
    WHERE catalog_product_entity_decimal.entity_id = catalog_product_entity.entity_id
    AND (catalog_product_entity_decimal.attribute_id = 64
    OR catalog_product_entity_decimal.attribute_id = 65
    OR catalog_product_entity_decimal.attribute_id = 68
    OR catalog_product_entity_decimal.attribute_id = 122 )
    AND catalog_product_entity.sku NOT LIKE 'hs_%%'"

    Cheers Paul

  • Jim Bryant

    Hello, I ran this code to change a price for a certain item and it changed it for the regular price but it added a special price and that price was the price that it was originally. so the special price is higher than the new price. it only shows up on the catagory page not the item page though....any thoughts to as why this is happening? 1.4x

  • Anoop

    Hi, Thanks for your articles. These articles are very helpful for newbie.
    I'm working on my second Magento project. I need your help. I've configured store for virtual product.
    This web store is basically for Game's live streaming.
    There are two category for products. I created custom attribute for products "Start Date/Time" for match. My requirement is : When certain game is organized on specific date/time, the product category should be change automatically.
    How can I update product category ? Is cron job is better idea or something else?
    Please help me.

    Thanks & Regards
    Anoop Namdev

  • MagentoNewbee

    Hello together,

    thanks for this great post its really helpful but I have a little problem. I'm trying to update my Prices in the Shop in an Interval. My Problem is, that I'm trying to update every product via SKU. can anybody help me?

    tanks a lot

  • MagentoNewbee

    good Morning Guys,

    i think I got it. If anybody needs it the following is the query:

    $write = Mage::getSingleton('core/resource')->getConnection('core_write');
    $write->query("
    UPDATE catalog_product_entity_decimal val
    SET val.value = ($priceToAdd)
    WHERE val.entity_id = (
    SELECT entity_id FROM catalog_product_entity cpe
    WHERE cpe.sku = '$artnr')
    ");

    greetz

  • Emilio

    hi,
    could it possible to increase the prices with 1%?
    Best regards
    Emilio

  • DT

    Wanted to say thank you for your work.

    Question also: Say I wanted to raise my prices by a percentage, would I just do the following?
    $priceToAdd = 6%;

    Thus adding 6% to every item

  • Dan CapitanDePlai

    Hello Fishpig!
    Searching the internet for a solution to my problem i stumbled upon this post and it's quite close for what im looking. I need to mass update products [ by an id range or by partial text in the "name" attribute ] and assign them to a category,cause in the process of adding them i forgot about the "categories" tab.
    Can you guide me a little? With my knowledge,i find magento's database structure quite overwhelming,i dont really know where to look.

  • Matthieu FLEITZ

    Hi!

    I'm not an sql expert. I've made this syntaxe to mass edit all of my grouped products with inspiration of your great work:

    UPDATE catalog_product_entity_decimal val
    SET val.value = (val.value + 100)
    WHERE val.attribute_id = (
    SELECT attribute_id FROM eav_attribute eav
    WHERE eav.entity_type_id = 4
    AND eav.attribute_code = 'price'
    )
    AND entity_id = (
    SELECT p.entity_id FROM catalog_product_entity p
    WHERE p.type_id = 'grouped'
    )

    I've got an issue tolding me that my sub query returns more than one word.

    Indeed, if I try to add a condition to select only one product ( with SKU for exemple ), it works:

    UPDATE catalog_product_entity_decimal val
    SET val.value = (val.value + 100)
    WHERE val.attribute_id = (
    SELECT attribute_id FROM eav_attribute eav
    WHERE eav.entity_type_id = 4
    AND eav.attribute_code = 'price'
    )
    AND entity_id = (
    SELECT p.entity_id FROM catalog_product_entity p
    WHERE p.type_id = 'grouped'
    AND p.sku = XXXXXXX
    )

    Any idea ? Thank you!

  • srinivas Vasuri
    srinivas Vasuri August 28, 2012 at 2:05 pm

    hi

    i'm srinivas, i want to change the prices of each simple products based on the seleting the attribute. how can i change the prices?

  • Steve

    Hi Fishpig!

    is there a way to mass update product prices for customer group?

    So maybe like this

    get product price then add or minus a percentage from that price and insert into the group price of the required customer group id

    an article on this would be great!

Post your comment

FishPig Ltd