CODEWITHSUNDEEP
phpmysqlmagento
Latheesan
Latheesan

Reputation: 24116

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

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.

Upvotes: 0

Views: 2270

Answers (3)

Harit
Harit

Reputation: 638

I recommend you to use Dataflow Profiles. (I guess you already know its usage, I am writing down the procedure in case it can be helpful to someone who doesn't know the usage of Dataflow Profiles)

  1. Go to System-> Import/Export -> Dataflow Profiles
  2. Run Export All Products Profile.
  3. From the csv exported to your var/export directory of your magento, extract the products whose special price you wish to insert/update.
  4. Update values of special_from_date, special_to_date and special_price fields in a new csv you wish to import.
  5. Import the new csv you just created by running Import All Products Profile.

This is a much reliable way. Hope it helps!!

Upvotes: 1

Ram Sharma
Ram Sharma

Reputation: 8809

Both the options will work but you have to do re-indexing of data in 2nd option. I am using the same sql for one of my project to set sale price. I am using it in cron file.

Direct query will save your time, if you have large amount of products.

Mage::getModel('index/process')->load(2)->reindexAll();

OR

php indexer.php --reindex catalog_product_price

Upvotes: 0

miszyman
miszyman

Reputation: 121

I'd recommend usage of INSERT with ON DUPLICATE KEY whilst the value for the special_price attribute could be not yet present. Didnt test your SQL but it looks ok - key things to handle is

  1. to retrieve the correct attribute_id from eav_attribute (not releted tip: don't do it in a subquery cause the performance will suffer from it)
  2. find the entity_id of the product by SKU from catalog_product_entity

another thing to keep in mind is when you use configurables you probably should set the price for configurable too, and all the simples (but this depends on your business approach)

Upvotes: 0

Related Questions