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.