How to delete all products and categories in Magento

Managing a Magento database involves various tasks, and there are instances where you might need to clear out categories and products efficiently. In this guide, we provide a custom SQL script for deleting all categories and products from a Magento database.

Disclaimer:

It is important to clarify the importance of creating a backup before running any script that involves data
 destruction. The script given is powerful and irreversible, so use it with caution, preferably in a
 staging environment, before making changes to a live production database.

Deleting Categories:

Let's begin by understanding the script's first section, which insists on deleting categories.

php

SET FOREIGN_KEY_CHECKS = 0;

    TRUNCATE TABLE catalog_category_entity;

    TRUNCATE TABLE catalog_category_entity_datetime; 
    TRUNCATE TABLE catalog_category_entity_decimal; 
    TRUNCATE TABLE catalog_category_entity_int; 
    TRUNCATE TABLE catalog_category_entity_text; 
    TRUNCATE TABLE catalog_category_entity_varchar; 
    TRUNCATE TABLE catalog_category_product; 
    TRUNCATE TABLE catalog_category_product_index;

    INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES ('1', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
    ('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

    INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES 
    ('1', '69', '0', '1', '1'),
    ('2', '46', '0', '2', '1'),
    ('3', '69', '0', '2', '1');

    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES 
    ('1', '45', '0', '1', 'Root Catalog'),
    ('2', '45', '0', '2', 'Default Category');

    SET FOREIGN_KEY_CHECKS = 1;

    DELETE FROM url_rewrite WHERE entity_type = 'category';

This code performs operations such as deleting table entries

Deleting Products:

Moving on to the second part of the script, which deals with the deletion of products.

php

SET FOREIGN_KEY_CHECKS = 0;
    TRUNCATE TABLE `catalog_category_product`;
    TRUNCATE TABLE `catalog_category_product_cl`;
    TRUNCATE TABLE `catalog_category_product_index`;
    TRUNCATE TABLE `catalog_category_product_index_replica`;
    TRUNCATE TABLE `catalog_category_product_index_store1`;
    TRUNCATE TABLE `catalog_category_product_index_store1_replica`;
    TRUNCATE TABLE `catalog_category_product_index_tmp`;
    TRUNCATE TABLE `catalog_product_attribute_cl`;
    TRUNCATE TABLE `catalog_compare_item`;
    TRUNCATE TABLE `catalog_product_bundle_option`;
    TRUNCATE TABLE `catalog_product_bundle_option_value`;
    TRUNCATE TABLE `catalog_product_bundle_price_index`;
    TRUNCATE TABLE `catalog_product_bundle_selection`;
    TRUNCATE TABLE `catalog_product_bundle_selection_price`;
    TRUNCATE TABLE `catalog_product_bundle_stock_index`;
    TRUNCATE TABLE `catalog_product_category_cl`;
    TRUNCATE TABLE `catalog_product_entity`;
    TRUNCATE TABLE `catalog_product_entity_datetime`;
    TRUNCATE TABLE `catalog_product_entity_decimal`;
    TRUNCATE TABLE `catalog_product_entity_gallery`;
    TRUNCATE TABLE `catalog_product_entity_int`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`;
    TRUNCATE TABLE `catalog_product_entity_text`;
    TRUNCATE TABLE `catalog_product_entity_tier_price`;
    TRUNCATE TABLE `catalog_product_entity_varchar`;
    TRUNCATE TABLE `catalog_product_index_eav`;
    TRUNCATE TABLE `catalog_product_index_eav_decimal`;
    TRUNCATE TABLE `catalog_product_index_eav_decimal_idx`;
    TRUNCATE TABLE `catalog_product_index_eav_decimal_replica`;
    TRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`;
    TRUNCATE TABLE `catalog_product_index_eav_idx`;
    TRUNCATE TABLE `catalog_product_index_eav_replica`;
    TRUNCATE TABLE `catalog_product_index_eav_tmp`;
    TRUNCATE TABLE `catalog_product_index_price`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_idx`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;
    TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;
    TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_downlod_idx`;
    TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_final_idx`;
    TRUNCATE TABLE `catalog_product_index_price_final_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_idx`;
    TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;
    TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_opt_idx`;
    TRUNCATE TABLE `catalog_product_index_price_opt_tmp`;
    TRUNCATE TABLE `catalog_product_index_price_replica`;
    TRUNCATE TABLE `catalog_product_index_price_tmp`;
    TRUNCATE TABLE `catalog_product_index_tier_price`;
    TRUNCATE TABLE `catalog_product_index_website`;
    TRUNCATE TABLE `catalog_product_link`;
    TRUNCATE TABLE `catalog_product_link_attribute`;
    TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
    TRUNCATE TABLE `catalog_product_link_attribute_int`;
    TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
    TRUNCATE TABLE `catalog_product_link_type`;
    TRUNCATE TABLE `catalog_product_option`;
    TRUNCATE TABLE `catalog_product_option_price`;
    TRUNCATE TABLE `catalog_product_option_title`;
    TRUNCATE TABLE `catalog_product_option_type_price`;
    TRUNCATE TABLE `catalog_product_option_type_title`;
    TRUNCATE TABLE `catalog_product_option_type_value`;
    TRUNCATE TABLE `catalog_product_price_cl`;
    TRUNCATE TABLE `catalog_product_relation`;
    TRUNCATE TABLE `catalog_product_super_attribute`;
    TRUNCATE TABLE `catalog_product_super_attribute_label`;
    TRUNCATE TABLE `catalog_product_super_link`;
    TRUNCATE TABLE `catalog_product_website`;
    TRUNCATE TABLE `catalog_url_rewrite_product_category`;
    TRUNCATE TABLE `catalogrule_product`;
    TRUNCATE TABLE `catalogrule_product_cl`;
    TRUNCATE TABLE `catalogrule_product_price`;
    TRUNCATE TABLE `catalogrule_product_price_replica`;
    TRUNCATE TABLE `catalogrule_product_replica`;
    TRUNCATE TABLE `cataloginventory_stock`;
    TRUNCATE TABLE `cataloginventory_stock_cl`;
    TRUNCATE TABLE `cataloginventory_stock_item`;
    TRUNCATE TABLE `cataloginventory_stock_status`;
    TRUNCATE TABLE `cataloginventory_stock_status_idx`;
    TRUNCATE TABLE `cataloginventory_stock_status_replica`;
    TRUNCATE TABLE `cataloginventory_stock_status_tmp`;
    TRUNCATE TABLE `downloadable_link`;
    TRUNCATE TABLE `downloadable_link_price`;
    TRUNCATE TABLE `downloadable_link_purchased`;
    TRUNCATE TABLE `downloadable_link_purchased_item`;
    TRUNCATE TABLE `downloadable_link_title`;
    TRUNCATE TABLE `downloadable_sample`;
    TRUNCATE TABLE `downloadable_sample_title`;
    TRUNCATE TABLE `product_alert_price`;
    TRUNCATE TABLE `product_alert_stock`;
    TRUNCATE TABLE `report_compared_product_index`;
    TRUNCATE TABLE `report_viewed_product_aggregated_daily`;
    TRUNCATE TABLE `report_viewed_product_aggregated_monthly`;
    TRUNCATE TABLE `report_viewed_product_aggregated_yearly`;
    TRUNCATE TABLE `report_viewed_product_index`;
    DELETE FROM url_rewrite where entity_type = 'product';
    INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
    INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
    INSERT  INTO `cataloginventory_stock`(`stock_id`,`website_id`,`stock_name`) VALUES (1,0,'Default');
    SET FOREIGN_KEY_CHECKS = 1;

This code follows the deletion of product-related URL rewrites.

Conclusion

Always have a backup, test in a controlled environment, and be aware of the implications of deleting categories and products. Use cases for such scripts include development scenarios or when resetting a testing environment. Remember to consider security aspects, ensuring that scripts with such capabilities are not exposed to the public and access controls are in place to prevent unauthorised usage.

This guide provides insights into a SQL script for deleting categories and products in a Magento database. Use it responsibly, following best practices for database management.

Elightwalk Technology Solutions has 5 years of experience in Magento development. Also, as an e-commerce development company, we try to provide the most efficient and secure solutions for managing databases. Our Magento developers have advanced knowledge and experience in developing a solution that is appropriate for your business's needs. Contact us for more information or any kind of Magento project development related discussion.

Table of contents

Jayram Prajapati

Full Stack Developer

Jayram Prajapati brings expertise and innovation to every project he takes on. His collaborative communication style, coupled with a receptiveness to new ideas, consistently leads to successful project outcomes.

Most Visited Blog

Blog

How to Create Crud Operations in Grid?

This step-by-step approach lets you creating CRUD operations in a grid. Learn how to integrate Create, Read, Update, and Delete functionality into your application's grid, allowing you to manage and change data efficiently.

Blog

How to add customer last logged in column customer grid in Magento 2?

Easily improve client insights with Magento 2! Learn how to add a 'Last Logged In' column to the customer grid with ease. To optimize client management and personalized experience, follow our step-by-step tutorial.

Blog

How to create a module in Magento 2: Sample Module A Step-by-Step Guide

Embark on Magento 2 journey with confidence! Our step-by-step tutorial module development. Learn how to create a Module to harness the potential of Magento 2 customization for personalized and efficient e-commerce experience.

Blog Info