Bulk Updating Magento Product Prices

A client today needed to export their pricing for products. This is normally not hard, the admin provides a simple system to do it. But, my client was using related products with pricing modifications involving super attributes and options.

Think "Shirt X costs $4.99. Large is another $2 and XL is $3 more".

That doesn't export well, and the table storage for the values is a huge mystery at first. Here's the finished solution to get those prices.

First, a couple important concepts:

The base price of the product is stored in catalog_product_entity_decimal, it's just an attribute value. The configurable pricing modifications are stored in catalog_product_super_attribute_pricing which relates directly from that table to both the eav_attribute, and eav_attribute_option_value tables directly.

The attribute ids in my system may not match yours, for me, 71 is 'name' of the product.

Here's the queries you'll need to pull the info, hope they make sense!

Base product price:

SELECT cped.`value` AS `price`, cpe.sku, cpev.value AS `name`
FROM catalog_product_entity_decimal cped
JOIN catalog_product_entity cpe ON cpe.entity_id = cped.entity_id
JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
WHERE cped.attribute_id IN (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('price')
)
AND cped.`value` > 0;
ORDER BY cpe.sku
SELECT cped.`value` AS `price`, cpe.sku, cpev.value AS `name`
FROM catalog_product_entity_decimal cped
JOIN catalog_product_entity cpe ON cpe.entity_id = cped.entity_id
JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
WHERE cped.attribute_id IN (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('price')
)
AND cped.`value` > 0;
ORDER BY cpe.sku

Pricing modifications:

SELECT cpe.sku, cpev.value AS `name`, ea.attribute_code, cpsal.value AS attribute_label, eaov.value AS option_label, cpsap.pricing_value AS price_modification
FROM catalog_product_super_attribute_pricing cpsap
JOIN catalog_product_super_attribute_label cpsal ON cpsal.product_super_attribute_id = cpsap.product_super_attribute_id
JOIN catalog_product_super_attribute cpsa ON cpsa.product_super_attribute_id = cpsap.product_super_attribute_id
JOIN catalog_product_entity cpe ON cpe.entity_id = cpsa.product_id
JOIN eav_attribute ea ON ea.attribute_id = cpsa.attribute_id
JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
JOIN eav_attribute_option_value eaov ON eaov.option_id = cpsap.value_index
ORDER BY cpe.sku
SELECT cpe.sku, cpev.value AS `name`, ea.attribute_code, cpsal.value AS attribute_label, eaov.value AS option_label, cpsap.pricing_value AS price_modification
FROM catalog_product_super_attribute_pricing cpsap
JOIN catalog_product_super_attribute_label cpsal ON cpsal.product_super_attribute_id = cpsap.product_super_attribute_id
JOIN catalog_product_super_attribute cpsa ON cpsa.product_super_attribute_id = cpsap.product_super_attribute_id
JOIN catalog_product_entity cpe ON cpe.entity_id = cpsa.product_id
JOIN eav_attribute ea ON ea.attribute_id = cpsa.attribute_id
JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
JOIN eav_attribute_option_value eaov ON eaov.option_id = cpsap.value_index
ORDER BY cpe.sku