I'm looking for a way to bulk update product special price, special to date & special from date. I prefer to use an SQL statement but are open for other suggestions

Looking forward for an answer

A. Mikkelsen


You can try something like this (obviously you should test thoroughly before using on live database):

Use this to update special_price value.

UPDATE `catalog_product_entity_decimal` AS `price`
INNER JOIN `eav_entity_type` AS `entity` ON `price`.`entity_type_id` = `entity`.`entity_type_id`
INNER JOIN `eav_attribute` AS `attribute` ON `price`.`attribute_id` = `attribute`.`attribute_id`
SET `price`.`value` = 123.0000
WHERE `price`.`entity_id` = 337
AND `entity`.`entity_type_code` = 'catalog_product'
AND `attribute`.`attribute_code` = 'special_price';

Update special_from_date and special_to_date values. Timezone should be UTC.

UPDATE `catalog_product_entity_datetime` AS `time`
INNER JOIN `eav_entity_type` AS `entity` ON `time`.`entity_type_id` = `entity`.`entity_type_id`
INNER JOIN `eav_attribute` AS `attribute` ON `time`.`attribute_id` = `attribute`.`attribute_id`
    `time`.`value` = CASE
    WHEN `attribute`.`attribute_code` = 'special_from_date' THEN '2014-03-01 00:00:00'
    WHEN `attribute`.`attribute_code` = 'special_to_date' THEN '2016-03-01 00:00:00'
WHERE `time`.`entity_id` = 337
AND `entity`.`entity_type_code` = 'catalog_product'
AND `attribute`.`attribute_code` IN ('special_from_date', 'special_to_date');
  • Can you please explain it more? What is 337? Where is the product_id that we want to update? Oct 10 '15 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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