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.
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
Leave a comment
Welcome to my Blog
Certifications
Honor
Recognition
Contributions
Categories
- Apache (2)
- ChatGPT (1)
- Domain name (2)
- eCommerce (2)
- htaccess (1)
- Humor (3)
- Instagram API (1)
- jQuery (4)
- JSON (1)
- Linux (10)
- Magento (142)
- Magento admin (58)
- Magento Certification (5)
- Magento error (13)
- Magento frontend (68)
- Magento Imagine (2)
- Magento Interview (5)
- Magento Master (2)
- Magento2 (10)
- Mobile (1)
- MySQL (7)
- OpenAI (1)
- OroCRM (2)
- Performance (2)
- PHP (8)
- Prototype JS (3)
- Security (4)
- Wordpress (3)
- XML (2)
Tag Cloud
500 internal server error admin answers attribute bug category checkbox checkout cookie customer difference domain name EAV error event extension interview invoice jquery linux magento magento2 magento admin magento error magento interview questions magento orm mysql observer order pinterest product products questions redirect register remove script session simplexml to array state status study guide tax url wordpress
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