Mass Price Updates in Magento: Rounding to .99 and Dealing with Tier Prices
As an e-commerce developer, you often face scenarios where you need to update prices across multiple products in bulk. In this post, we’ll address a common challenge - updating product prices by a percentage while rounding them down to the nearest .99 cent.
The Problem: Rounding Down to the Nearest Cent
Let’s say you want to reduce all prices in a specific category by 10%. If a product has an original price of $120, calculating the new price ($120 * 0.9) gives us $108. However, if your pricing strategy requires rounding down to the nearest cent, this amount should be rounded up to $108.99.
The Solution: Using MySQL ROUND Function
To achieve this, you can leverage the ROUND() function in MySQL while updating your prices. Here’s a modified version of the query provided:
UPDATE catalog_product_entity_decimal
SET value = ROUND(value * 0.9, 2)
WHERE attribute_id = 64 AND
entity_in (SELECT product_id FROM catalog_category_product
WHERE category_id = X);
This query multiplies each price by 0.9 and then rounds it to two decimal places using ROUND(value * 0.9, 2). This ensures that the new prices are rounded down to the nearest cent.
Handling Tier Prices
Dealing with tier pricing introduces an additional layer of complexity. Tier pricing allows you to offer different prices based on quantity purchased. To update these tier prices by the same percentage and round them similarly:
-
First, identify the attribute IDs for your tier price fields. These are typically
tier_price_*attributes. -
Then, create a separate query for each tier price field, applying the same logic as above but using the corresponding attribute ID:
UPDATE catalog_product_entity_decimal
SET value = ROUND(value * 0.9, 2)
WHERE attribute_id IN (65, 66, 67) AND -- replace with your actual IDs
entity_in (SELECT product_id FROM catalog_category_product
WHERE category_id = X);
Conclusion
Updating prices in bulk while maintaining a specific rounding strategy can be challenging. By leveraging MySQL’s ROUND() function and creating strategic queries, you can effectively update both regular and tiered prices across multiple products.
Remember, always back up your data before performing mass updates to ensure data integrity. If the volume of your product catalog is large or the performance impact concerns you, consider using Magento’s built-in import/export functionality for these operations.
For more advanced pricing strategies and customizations, I recommend exploring the Laravel Company blog for in-depth tutorials and best practices in e-commerce development.