Update price attribute and save attribute

Question

I'm updating a bunch of product prices at once, I plan on doing this using a shell script that loads a SKU and the price to update. I also need to make sure this only applies to a specific store. So here is how I planned to go about it:

• Load my list of products into an array
• Include Mage.php
• Load the store scope
• Load each product in my list by SKU
• Change the price using setPrice
• Save the product attribute without having to save the entire product (I have a lot of SKUs to update)
• Iterate through each product using the same process

My question is, can this be done with core attributes? Or does it need a full product save? I've seen several posts and scripts of people using saveAttribute with custom attributes under the default store, but the last time I attempted this it had huge reprocussions. I've tried a CSV using the import and it won't work right, and magmi is failing due to some strange errors, so I'm forced to take matters into my own hands.

Answer 1

Let's assume you have an array like this with the sku and prices.

$prices = array(
    'sku1' => '12.99',
    'sku2' => '15.00',
    .... 
);

and you only need to change the price for store view 2.

Here is a fast way to do it.

Since the price can be global or has the website scope, you will also need to change the price for all the store views in the same website as the store view with id 2. The next script will do that for you, just make sure that prior to running it, you set the price scope to 'website'.

$storeId = 2;
$idsByPrice = array();

foreach ($prices as $sku=>$price) {
    $id = Mage::getSingleton('catalog/product')->getIdBySku($sku);
    if ($id) { //if the product exists
        if (!isset($idsByPrice[$price])) {
            $idsByPrice[$price] = array();
        } 
        //group all skus by price so you will have less updates.
        $idsByPrice[$price][] = $id; 
    }
}

//now you have an array of product ids grouped by price
//$idsByPrice = array(
//    '12.99' => array(2,3,7,12),
//    '15.00' => array(9,44,22),...
//)

foreach ($idsByPrice as $price=>$ids) {
    Mage::getSingleton('catalog/product_action')->updateAttributes(
        $ids,  //ids to update
        array('price'=>$price), //attributes to update
        $storeId //store view to update the attribtues
    );
}

Include Mage.php at the top of the script and run it. back up your db first, just in case. And your indexes may need rebuilding when you are done.

Answer 2

I'd go the following route:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('*');
$collection->addStoreFilter($storeId);
$connection_write = Mage::getSingleton('core/resource')->getConnection('core_write');
foreach ($collection as $product){
    $sql = 'UPDATE `catalog_product_entity_decimal` SET `value` = ? WHERE `entity_id` = ? AND `attribute_id` = ?';
    $connection_write->query($sql, array($your_desired_price, $product->getId(), $price_attribute_id);
}

This uses direct SQL to update the database pricing directly, which will run exceptionally quickly and save resources.

The $price_attribute_id can be identified by:

Catalog → Attributes → Manage Attributes

Filter by 'price' and click onto it. Then look at the URL to identify the attribute ID:

/catalog_product_attribute/edit/attribute_id/60/key/f9596905e008f59edfa9de9d9d438c8c/

The /attribute_id/60/ in this instance indicates to me that 60 is the attribute id for price, yours may probably be similar.

Let me know if you run into any problems.