Fragmented Thought

Bulk Updating Magento Product Prices

By

Published

Lance Gliser

Heads up! This content is more than six months old. Take some time to verify everything still works as expected.

Had a client that needed to increase their prices by 1.58%, across the entire store. They used base product prices on configurable products with super options that had their own price modifications.

Think: Product A - $10. Option A ( 12 oz ) + $2

The sql queries below will update the product tables as required. Note that these work only on static price adjustments, not percentage. Though, that should be easy enough to figure out given the below tables.

This worked as of Magento community version 1.7.0.2.

Sql

Base prices:

UPDATE catalog_product_entity_decimal SET `value` = ROUND( `value` + (`value` * .0185), 2) WHERE attribute_id IN ( SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('price', 'special_price') ) AND `value` > 0

Product Super Options Costs

-- Positive Adjustments UPDATE catalog_product_super_attribute_pricing SET pricing_value = ROUND( pricing_value + ( pricing_value \* .0185 ), 2 ) WHERE pricing_value > 0 AND is_percent = 0 -- Negative Adjustments UPDATE catalog_product_super_attribute_pricing SET pricing_value = ROUND( pricing_value - ( pricing_value \* .0185 ), 2 ) WHERE pricing_value < 0 AND is_percent = 0

Reindex

After completing, make sure to go back into the admin, and reindex pricing!