How do you set the special_price of product in magento via direct SQL query?

Question

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.

• Stefan Gregori: maybe my answer on this magento.stackexchange.com/questions/8943/… is helpful. maybe more simple way.

Answer 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
 );
}

• snh_nl: 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?
• Marius: if the price is different you have to loop through your products one by one.
• snh_nl: :thumbsup: thanks Marius - and this works for all attribute updates? sku? price? specialprice? etc (so both 'special' and attributes that live in EAV). thx

Answer 2

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.