Aug 9, 2012
kalpesh

Magento: How to delete/remove all products from all categories

When you are using some script to automatically add products to categories from XLS file to your Magento system, you may need to delete all products frequently for testing purpose. It’s not as easy to mark all products and press delete button in admin. No, it’s not like that, you need to delete all products, remove them from their linked categories, reset the inventory, etc. so many things to do.

Below is the MySQL queries that will make your task easier. By running all the queries, it will delete all the products in your Magento system. Use it carefully, it’s going to delete everything without an option to rollback.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
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_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
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_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
DELETE FROM catalog_product_flat_1;
DELETE FROM catalog_product_flat_10;
DELETE FROM catalog_product_flat_11;
DELETE FROM catalog_product_flat_12;
DELETE FROM catalog_product_flat_13;
DELETE FROM catalog_product_flat_14;
DELETE FROM catalog_product_flat_15;
DELETE FROM catalog_product_flat_16;
DELETE FROM catalog_product_flat_17;
DELETE FROM catalog_product_flat_18;
DELETE FROM catalog_product_flat_19;
DELETE FROM catalog_product_flat_2;
DELETE FROM catalog_product_flat_20;
DELETE FROM catalog_product_flat_21;
DELETE FROM catalog_product_flat_22;
DELETE FROM catalog_product_flat_23;
DELETE FROM catalog_product_flat_24;
DELETE FROM catalog_product_flat_25;
DELETE FROM catalog_product_flat_26;
DELETE FROM catalog_product_flat_27;
DELETE FROM catalog_product_flat_28;
DELETE FROM catalog_product_flat_29;
DELETE FROM catalog_product_flat_3;
DELETE FROM catalog_product_flat_30;
DELETE FROM catalog_product_flat_31;
DELETE FROM catalog_product_flat_32;
DELETE FROM catalog_product_flat_33;
DELETE FROM catalog_product_flat_34;
DELETE FROM catalog_product_flat_35;
DELETE FROM catalog_product_flat_36;
DELETE FROM catalog_product_flat_37;
DELETE FROM catalog_product_flat_4;
DELETE FROM catalog_product_flat_5;
DELETE FROM catalog_product_flat_6;
DELETE FROM catalog_product_flat_7;
DELETE FROM catalog_product_flat_8;
DELETE FROM catalog_product_flat_9;
SET FOREIGN_KEY_CHECKS = 1;

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`,`stock_name`) values (1,'Default');

Now go to System > Index Management, and re-index all indexes.

Hope this helps!

5 Comments

  • Dear Kalpesh, I am a beginner and because a was testing ordering products for the netherlands I want to clear the whole products to start from scratch… I notice that for several products the ID was not correct. Thats why I want to clear all the products and start with one product, importing true csv file. But I dont now how to use this MySQL queries? Please help…

    • Were you able to do that? Email me if you are still looking for help. Thanks!

      • Hi Kalpesh, not yet because I having now problems with getting edit my products:
        I was looking on the web the explaning of this report log:

        a:5:{i:0;s:89:”Source model “layoutable/system_configid” not found for attribute “layoutbuddy_config_id””;i:1;s:3882:”#0 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Eav/Model/Entity/Attribute/Abstract.php(387): Mage::exception(‘Mage_Eav’, ‘Source model “l…’) #1 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/Block/Widget/Form.php(201): Mage_Eav_Model_Entity_Attribute_Abstract->getSource() #2 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/Block/Catalog/Product/Edit/Tab/Attributes.php(70): Mage_Adminhtml_Block_Widget_Form->_setFieldset(Array, Object(Varien_Data_Form_Element_Fieldset), Array) #3 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/Block/Widget/Form.php(144): Mage_Adminhtml_Block_Catalog_Product_Edit_Tab_Attributes->_prepareForm() #4 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Block/Abstract.php(918): Mage_Adminhtml_Block_Widget_Form->_beforeToHtml() #5 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/Block/Catalog/Product/Edit/Tabs.php(79): Mage_Core_Block_Abstract->toHtml() #6 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Adminhtml_Block_Catalog_Product_Edit_Tabs->_prepareLayout() #7 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout)) #8 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock(‘adminhtml/catal…’, ‘product_tabs’) #9 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock(‘adminhtml/catal…’, ‘product_tabs’) #10 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element)) #11 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element)) #12 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks() #13 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks() #14 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/Controller/Action.php(275): Mage_Core_Controller_Varien_Action->loadLayout(Array, true, true) #15 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Adminhtml/controllers/Catalog/ProductController.php(250): Mage_Adminhtml_Controller_Action->loadLayout(Array) #16 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Catalog_ProductController->editAction() #17 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch(‘edit’) #18 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) #19 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch() #20 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/app/Mage.php(684): Mage_Core_Model_App->run(Array) #21 /home/ran/domains/ranestruisvlugt.nl/public_html/magento/index.php(87): Mage::run(”, ‘store’) #22 {main}”;s:3:”url”;s:91:”/magento/index.php/admin123/catalog_product/edit/id/5/key/69bf5fa438bba3f70450ab2f07cc2f2a/”;s:11:”script_name”;s:18:”/magento/index.php”;s:4:”skin”;s:5:”admin”;}

  • Dear Kalpesh if you could help that will be fine!

  • This is a great help to me but I am facing some issues when using this code. I ran all the sql commands successfully and then re-indexed from admin and then importing a csv file using default magento import option its throwing following error:

    SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_name`.`catalog_product_option_type_title`, CONSTRAINT `FK_C085B9CF2C2A302E8043FDEA1937D6A2` FOREIGN KEY (`option_type_id`) REFERENCES `catalog_product_option_type_value` (`option_type_id), query was: INSERT INTO `catalog_product_option_type_title` (`option_type_id`,`store_id`,`title`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `title` = VALUES(`title`)

    How can I get rid of that? Magento version is 1.9.3.2

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications

Honor

Recognition

Magento top 50 contributors

Magento top 50 contributors

Contributions