0

I need to mass update special price from a CSV File (which tells me the sku, the special price, to/from date).

At the moment I do it using magento models method like this (after parsing the CSV rows) in a foreach loop:

$p = Mage::getModel('catalog/product');
$product_id = $p->getIdBySku($product['SKU']);
if (!$product_id) {
    throw new Exception('Product Does Not Exists');
}
$p->setStoreId($store_id)->load($product_id);
$p->setSpecialPrice(floatval($product['Price']));
$p->setSpecialFromDate($product['FromDate']);
$p->setSpecialFromDateIsFormated(true);
$p->setSpecialToDate($product['ToDate']);
$p->setSpecialToDateIsFormated(true);
$p->save();
$p = null;

This is okay when a handful of product needs updating. However when you update 100+ products, this becomes incredibly slow and it affects the site performance also.

Is there anyway I can mass import special price and also set the dates via a direct SQL Query?


Whilst researching this issue, I have found a possible solution (based on this article http://fishpig.co.uk/magento/tutorials/update-prices-sql) with setting the special_price in the database directly like this:

Note: bd_ is my table prefix

UPDATE bd_catalog_product_entity AS CPE
INNER JOIN bd_catalog_product_entity_decimal AS CPED ON CPED.entity_id = CPE.entity_id
SET CPED.`value` = 2.99 -- New Special Price
WHERE
    CPED.attribute_id = (
        SELECT
            attribute_id
        FROM
            bd_eav_attribute eav
        WHERE
            eav.entity_type_id = 4
        AND eav.attribute_code = 'special_price'
    )
AND CPED.store_id = 1   -- Retail Store
AND CPE.sku = 'ABS049'  -- Sku Being Updated

This appears to be working (i can see the value in magento UI updated). I am not yet sure if this is the correct way to go about setting the special price in the database directly.

Also, I've not yet worked out how to set the from/to dates.

Any help on this will be much appreciated.

1

2 Answers 2

1

Let's say that $storeId is the store for which you want to update the price $specialPrice is your special price, $sku is your product SKU, $fromDate and $toDate are...well, you get the idea.

Now run this code:

$id = Mage::getSingleton('catalog/product')->getIdBySku($sku);
if ($id) {
    Mage::getSingleton('catalog/product_action')->updateAttributes(
        array($id), //array with ids to update
        array( //array with attributes to update
            'special_price' => $specialPrice,
            'special_from_date' => $fromDate,
            'special_to_date' => $toDate,
        ),
        $storeId //store id to update. If you want to update the global value use 0 in here
    );
}
3
  • Suppose all the individual products have other special_price: would you then loop and do it 1 ID at a time? Or can $specialPrice in the above also be an array containing ever products special price (same number of keys for both arrays).. .hope this is clear?
    – snh_nl
    Commented Jun 29, 2017 at 14:25
  • if the price is different you have to loop through your products one by one.
    – Marius
    Commented Jun 29, 2017 at 14:27
  • :thumbsup: thanks Marius - and this works for all attribute updates? sku? price? specialprice? etc (so both 'special' and attributes that live in EAV). thx
    – snh_nl
    Commented Jun 29, 2017 at 14:29
0

If you want to update existing products then No need to do changes by manually. Just import CSV that should contain only those attribute as columns which are needed to update. SKU is required field so don't forget to include this in your CSV.

It will import products very fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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