Where is the table that holds the "Special Price" in Magento?

Where is the table that holds the "Special Price" in Magento?

September 1, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Where is the table that holds the "Special Price" in Magento?

Unveiling the Magento Special Price Table

Are you seeking to update your Magento store’s special prices in bulk using MySQL and PHP? You’ve come to the right place! Let’s dive into finding that elusive “Special price” table.

Understanding Magento Database Structure

Magento, being an open-source e-commerce platform, uses a relational database to manage its data. The core tables are prefixed with catalog_, core_, or sales_. When it comes to product prices, two key tables come into play:

  1. catalog_product_entity: This is the main table containing basic product information, including the standard price (price).

  2. **catalog_product_index_price: ** This complex index table holds various price-related data.

Locating the Special Price Field

Now, where does Magento store those special prices? The answer lies within the catalog_product_index_price table.

The field you’re looking for is called tax_class_id. However, this isn’t immediately obvious. Here’s a quick breakdown:

  • The price field in this table holds the final price after applying tax.
  • The actual special price before tax is stored separately and isn’t straightforward to extract without additional logic.

Extracting Special Prices

To extract special prices, you’ll need to join multiple tables together. Here’s an example SQL query that fetches both standard and special prices for your products:

SELECT
    e.entity_id,
    e.sku,
    e.name,
    e.type_id,
    e.price AS regular_price,
    IFNULL(p.min_price, e.price) AS special_price
FROM
    catalog_product_entity AS e
LEFT JOIN
    (
        SELECT
            entity_id,
            MIN(price) AS min_price
        FROM
            catalog_product_index_price
        WHERE
            website_id = 0
            AND customer_group_id IN (1,2,3)
        GROUP BY
            entity_id
    ) p ON e.entity_id = p.entity_id;

In this query:

  • e is the alias for catalog_product_entity.
  • The subquery (p) fetches the minimum price from catalog_product_index_price, which represents special prices.
  • We join these two tables on entity_id.

Updating Prices in Bulk

Once you have your data, you can update the prices using a PHP script. Here’s a simplified example:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=magentodb', 'username', 'password');

$data = [
    1 => ['price' => 99.99],
    2 => ['special_price' => 85.00],
];

foreach ($data as $id => $fields) {
    if (isset($fields['price'])) {
        $stmt = $pdo->prepare("UPDATE catalog_product_entity SET price = ? WHERE entity_id = ?");
        $stmt->execute([$fields['price'], $id]);
    }
    if (isset($fields['special_price'])) {
        // Update special price logic goes here...
    }
}
?>

Conclusion

In Magento, the catalog_product_index_price table is your friend when dealing with special prices. The field isn’t labeled as “special price,” but you can extract it using the provided SQL query.

Remember, updating prices in bulk requires careful handling to ensure data integrity and performance. Always backup your database before performing mass updates.

If you’re looking for more advanced Magento development insights or want to discuss custom solutions, feel free to reach out to our team at Laravel Company.

Happy coding!

Catalog Indexing Database Pricing

Our network