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!