Cleanly delete all categories in Magento

If you have been working on a demo store - or yours is still in the early stages of construction, you will probably find it necessary to remove all the categories to start fresh at some point.

Thanks to CasualCommerce, there is a pretty straightforward MySQL "script" to clear out the tables and start fresh.

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`,`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);
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);
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');
  • Douglas

    Just ran this and it produced an error and, apparently, wiped out my ability to create new categories since I now only have white screen in my Manage Categories section. The buttons to create new categories is gone.

    I'm running Magento ver. 1.3.2.4

    • Hi Douglas,

      The SQL is okay - but WordPress add's "fancy" apostrophes which MySQL doesn't understand.

      There is a fancy quote remover on-line (https://www.sonassi.com/knowledge-base/lifesaving-online-tools-for-a-web-developer/).

      If you run the above MySQL through it, it will convert *some* of the fancy quotes. Basically, it just needs a find and replace on the left curly -‘- and right curly -′- quotes to standard apostrophes.

      I've edited the code above with curly quotes for you 😉

  • Douglas

    It was a character replacement problem 🙂

  • Use this for magento 1.4.1.1

    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,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);
    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');
    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');

  • it works well ! 3Q . ^_^

  • I recommend setting the following

    SET FOREIGN_KEY_CHECKS = 0;

    before the script above and

    SET FOREIGN_KEY_CHECKS = 1;

    after the script above to prevent errors due to foreign key restraints.

  • Hello,
    I have a category who are undeleteble!
    I don't know if it's a bug or feature...

    If I click on it, I don't have the delete button fro the top...
    I tried to find it in the DB but no success...
    How I can remove those categories?

  • Ashutosh Bhardwaj

    i am fed up with the complexity , confusion and lack of straight forward documentation on magento. duh.. they cant even offer a list of categories that can be selected and deleted ?? also if i want to make some categories as subcategory of another category .. i'll have to delete them and crate them again..?? this is sucking bad.