Magento - Delete selected products with SQL query

Magento - Delete selected products with SQL query

August 22, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento - Delete selected products with SQL query

Deleting Bulk Products from Magento using SQL

Managing large product databases in Magento can become cumbersome and time-consuming when you need to perform bulk operations like deleting multiple products at once. The built-in interface may not be optimal for handling hundreds of thousands of records, leading to timeouts and performance issues.

In such cases, turning directly to the MySQL database using SQL queries is often the most efficient solution. However, Magento does not store all product data in a single table. It’s distributed across multiple tables like catalog_product_entity, catalog_product_option_value, catalog_category_product etc. Deleting from one table without considering others can lead to orphaned records and database inconsistencies.

Let me guide you through the process of deleting selected products starting with “NLA” in their title using a series of SQL queries that cover all relevant tables.

Step 1: Identify Product IDs

First, we need to find the product IDs (entity_id) for the products whose titles start with ‘NLA’.

SELECT entity_id
FROM catalog_product_entity
WHERE name LIKE '*NLA%'

This query will return a list of IDs that you can use in subsequent delete queries.

Step 2: Delete from Main Product Table

Use this query to delete records from the main catalog_product_entity table. Replace %ID1%, %ID2%, ... with the actual product IDs separated by commas.

DELETE FROM catalog_product_entity
WHERE entity_id IN (%ID1%, %ID2%, ...)

Step 3: Delete Associated Records

The following queries delete associated records from other tables. Make sure to execute them in the order listed to avoid cascading deletion errors:

  • Delete from catalog_product_option_value:
DELETE FROM catalog_product_option_value
WHERE option_id IN (
    SELECT option_id
    FROM catalog_product_option
    WHERE product_id IN (%ID1%, %ID2%, ...)
)
  • Delete from catalog_category_product:
DELETE FROM catalog_category_product
WHERE product_id IN (%ID1%, %ID2%, ...)
  • Delete from catalog_product_attribute_int and catalog_product_attribute_decimal:

These tables store attribute values for integer and decimal types respectively.

DELETE FROM catalog_product_attribute_int WHERE product_id IN (%ID1%, %ID2%, ...)

DELETE FROM catalog_product_attribute_decimal WHERE product_id IN (%ID1%, %ID2%, ...)
  • Delete from catalog_product_option:

This table stores the options (like size, color etc.) for each product.

DELETE FROM catalog_product_option WHERE product_id IN (%ID1%, %ID2%, ...)

Conclusion

Deleting products in bulk using SQL queries can significantly reduce processing time compared to the Magento interface. However, it requires careful handling of multiple tables and understanding of the database schema.

It’s always recommended to take a full backup before running any delete operations on your production database. For more detailed instructions tailored to your specific use case, I recommend consulting with an experienced Magento developer or checking out our Magento Performance Optimization Guide for additional best practices.

Remember, while SQL queries can be powerful tools, they should be used judiciously and with a thorough understanding of the database structure to prevent data corruption or loss.

Let me know if you have any further questions!

Catalog Database Performance Errors

Our network