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

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

October 14, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How do you set the special_price of product in magento via direct SQL query?

Setting Special Price of Products via Direct SQL Query in Magento

Managing special prices for multiple products in Magento can be quite challenging, especially when you need to update hundreds of them simultaneously. While Magento provides models like Mage::getModel('catalog/product') to handle this task programmatically, it may become incredibly slow and impact site performance if used on a large scale.

In such cases, performing direct database queries to update the special price can offer substantial speed improvements. Let’s explore how you can achieve this efficiently.

Understanding the Magento Product Entity Structure

Magento stores product data across multiple tables in its database. For our purpose, we’ll be focusing on two key entities:

  1. catalog_product_entity: Contains core product information like SKU, name, price, etc.
  2. catalog_product_entity_decimal: Stores decimal values for product attributes, including the special price.

Setting Special Price via Direct SQL Query

To update the special price for a specific product via an SQL query, you can use the following approach:

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 IN (
        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   -- Store ID (usually 0 for default store)
AND CPE.sku = 'ABS049'  -- SKU of the product to be updated

In this query:

  • Replace bd_ with your actual table prefix.
  • Set the desired special price value in the SET CPED.value = ... line.
  • Update the store ID and SKU as per your requirements.

Setting Special Price for Multiple Products

To update special prices for multiple products, you can use a similar approach but modify the WHERE clause to filter by an array of SKUs or use subqueries based on your specific needs. However, always ensure that proper indexes are in place to optimize query performance when dealing with large datasets.

Setting Special Price Dates via SQL

Updating the special price is straightforward, but setting the from and to dates requires a slightly different approach. Magento stores these dates as timestamps in the catalog_product_entity_int table for attributes with IDs corresponding to ‘special_from_date’ and ‘special_to_date’.

You can update these dates using similar SQL queries:

UPDATE bd_catalog_product_entity_int AS CPEI
SET CPEI.value = UNIX_TIMESTAMP('2023-12-01 00:00:00') -- Set Special From Date as Unix Timestamp
WHERE
    CPEI.entity_id IN (
        SELECT
            entity_id
        FROM
            bd_catalog_product_entity CPED
        WHERE
            CPED.sku = 'ABS049'
    )
AND CPEI.attribute_id = (
    SELECT
        attribute_id
    FROM
        bd_eav_attribute eav
    WHERE
        eav.entity_type_id = 4
    AND eav.attribute_code = 'special_from_date'
);

-- Repeat similar query for special_to_date using the required Unix Timestamp value

Conclusion

While Magento provides a robust API for managing products programmatically, performing direct database queries can offer significant performance benefits when dealing with large-scale updates like mass special price adjustments.

However, it is crucial to thoroughly test any direct SQL queries before deploying them in a production environment. Always ensure that proper backup and error handling mechanisms are in place.

For more detailed insights into Magento’s data structure and optimization techniques, consider exploring the resources available on platforms like Laravel Company.

Happy coding!

Catalog Database Performance Configuration

Our network