You are viewing an old revision of this post, from March 30, 2015 @ 10:50:38. See below for differences between this version and the current revision.

Removing products and categories from the database

Below queries will definitely delete products from database but still few tables have entries for e.g. catalog_product_flat_1, catalog_product_flat_2,etc. So its not wise to delete using below queries.

Removing products and categories from the database

 

These instructions have been copied from:

http://www.magentocommerce.com/boards/viewreply/106786/

Truncate products

This should remove all the products and their related records.

  1. TRUNCATE TABLE `catalog_product_bundle_option`;
  2. TRUNCATE TABLE `catalog_product_bundle_option_value`;
  3. TRUNCATE TABLE `catalog_product_bundle_selection`;
  4. TRUNCATE TABLE `catalog_product_entity_datetime`;
  5. TRUNCATE TABLE `catalog_product_entity_decimal`;
  6. TRUNCATE TABLE `catalog_product_entity_gallery`;
  7. TRUNCATE TABLE `catalog_product_entity_int`;
  8. TRUNCATE TABLE `catalog_product_entity_media_gallery`;
  9. TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
  10. TRUNCATE TABLE `catalog_product_entity_text`;
  11. TRUNCATE TABLE `catalog_product_entity_tier_price`;
  12. TRUNCATE TABLE `catalog_product_entity_varchar`;
  13. TRUNCATE TABLE `catalog_product_link`;
  14. TRUNCATE TABLE `catalog_product_link_attribute`;
  15. TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
  16. TRUNCATE TABLE `catalog_product_link_attribute_int`;
  17. TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
  18. TRUNCATE TABLE `catalog_product_link_type`;
  19. TRUNCATE TABLE `catalog_product_option`;
  20. TRUNCATE TABLE `catalog_product_option_price`;
  21. TRUNCATE TABLE `catalog_product_option_title`;
  22. TRUNCATE TABLE `catalog_product_option_type_price`;
  23. TRUNCATE TABLE `catalog_product_option_type_title`;
  24. TRUNCATE TABLE `catalog_product_option_type_value`;
  25. TRUNCATE TABLE `catalog_product_super_attribute`;
  26. TRUNCATE TABLE `catalog_product_super_attribute_label`;
  27. TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
  28. TRUNCATE TABLE `catalog_product_super_link`;
  29. TRUNCATE TABLE `catalog_product_enabled_index`;
  30. TRUNCATE TABLE `catalog_product_website`;
  31. TRUNCATE TABLE `catalog_product_entity`;
  32.  
  33. TRUNCATE TABLE `cataloginventory_stock`;
  34. TRUNCATE TABLE `cataloginventory_stock_item`;
  35. TRUNCATE TABLE `cataloginventory_stock_status`;
  36.  
  37. INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
  38. 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');
  39. INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');

In magento 1.5 you need to add in cataloginventory_stock., I added a record with stock_id 1 and stock_name to Default. Without this there will be a foreign key constraint fail, see http://www.magentocommerce.com/boards/v/viewthread/41764/#t169087for further explanation

Truncate categories

This should remove all the categories and their related records. It will also create new Root category.

  1. TRUNCATE TABLE `catalog_category_entity`;
  2. TRUNCATE TABLE `catalog_category_entity_datetime`;
  3. TRUNCATE TABLE `catalog_category_entity_decimal`;
  4. TRUNCATE TABLE `catalog_category_entity_int`;
  5. TRUNCATE TABLE `catalog_category_entity_text`;
  6. TRUNCATE TABLE `catalog_category_entity_varchar`;
  7. TRUNCATE TABLE `catalog_category_product`;
  8. TRUNCATE TABLE `catalog_category_product_index`;
  9.  
  10. INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);
  11. INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,32,1,2,1);
  12. INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');
  13.  
  14. /**** Magento ver. 1.6.x.x ****/
  15.  
  16. TRUNCATE TABLE `catalog_category_entity`;
  17. TRUNCATE TABLE `catalog_category_entity_datetime`;
  18. TRUNCATE TABLE `catalog_category_entity_decimal`;
  19. TRUNCATE TABLE `catalog_category_entity_int`;
  20. TRUNCATE TABLE `catalog_category_entity_text`;
  21. TRUNCATE TABLE `catalog_category_entity_varchar`;
  22. TRUNCATE TABLE `catalog_category_product`;
  23. TRUNCATE TABLE `catalog_category_product_index`;
  24.  
  25. INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','0000-00-00 00:00:00','1',1,0,1), (2,3,3,1,'0000-00-00 00:00:00','0000-00-00 00:00:00','1/2','1','1','0');
  26. INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,36,0,2,1),(3,3,61,0,2,1),(4,3,44,0,2,NULL),(5,3,45,0,2,1),(6,3,62,0,2,1),(7,3,63,0,2,1),(8,3,64,0,2,NULL);
  27. INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,35,0,2,'Default Category'),(3,3,37,0,2,'default-category'),(4,3,40,0,2,NULL),(5,3,43,0,2,'PRODUCTS'),(6,3,52,0,2,NULL),(7,3,55,0,2,NULL);
  28.  
  29. /**** Magento ver. 1.6.x.x ****/

Truncate customers

This should remove all the customers and their related records.

  1. TRUNCATE TABLE `customer_address_entity`;
  2. TRUNCATE TABLE `customer_address_entity_datetime`;
  3. TRUNCATE TABLE `customer_address_entity_decimal`;
  4. TRUNCATE TABLE `customer_address_entity_int`;
  5. TRUNCATE TABLE `customer_address_entity_text`;
  6. TRUNCATE TABLE `customer_address_entity_varchar`;
  7. TRUNCATE TABLE `customer_entity`;
  8. TRUNCATE TABLE `customer_entity_datetime`;
  9. TRUNCATE TABLE `customer_entity_decimal`;
  10. TRUNCATE TABLE `customer_entity_int`;
  11. TRUNCATE TABLE `customer_entity_text`;
  12. TRUNCATE TABLE `customer_entity_varchar`;
 

Revisions

  • March 30, 2015 @ 10:50:38 [Current Revision] by admin
  • March 30, 2015 @ 10:50:38 by admin

Revision Differences

There are no differences between the March 30, 2015 @ 10:50:38 revision and the current revision. (Maybe only post meta information was changed.)

No comments yet.

Leave a Reply