Magento 2: How to delete all categories at once?

Question

We have been experimenting with syncing our existing database(not Magento) to Magento 2.

In the process, we have created over 1000 categories, but then we rearranged all the IDs to sync with our system. The problem is that I can no longer import the new categories as I get the error "URL key for specified store already exists." and we are getting duplicate categories now.

I think the best solution would be to clear them all out and import fresh. I found this tutorial for 1.9

https://gist.github.com/jklance/9664371

When I look at the new database I notice small differences

entity_type_id is in all the old category tables but none of the new ones.
new database has an additional table catalog_category_product_index_tmp

My questions are these,

can I use that code I found and modify to remove entity_type_id and add TRUNCATE TABLE catalog_category_product_index_tmp;
or is there more I need to modify?
or can you provide me a clean script to remove all categories?

we only have 1 test product in the system, so no need to worry about them

hanish singla · Accepted Answer · 2016-07-11 06:27:29Z

This one worked for me in Magento ver. 2.1.0

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

Just that so other people know, this worked perfectly for me. The one in the other answer did not work for me even after correcting the typos. — Hassan Al-Jeshi, Commented Sep 27, 2016 at 6:42
Gives problems when creating products afterwards in Magento 2.2.3. Stuck when creating product: No such entity with id = 3 — Condor, Commented Apr 10, 2018 at 7:53

Bogdan Le · Accepted Answer · 2016-10-07 08:59:36Z

This one will remove all categories in Magento EE 2.1

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`, `created_in`, `updated_in`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES 
('1', '1', '2', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '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`, `row_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`, `row_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';
DELETE FROM `sequence_catalog_category` WHERE sequence_value > 2;

Working fine 2.4.6
– Mahendra
Commented Oct 10, 2023 at 11:51 — Mahendra, Commented Oct 10, 2023 at 11:51

Liz Eipe C · Accepted Answer · 2017-08-12 11:25:00Z

The following delete all the categories magento programmatically except the root category.

function deleteAllCategories($objectManager) {

$categoryFactory = $objectManager->get('Magento\Catalog\Model\CategoryFactory');
$newCategory = $categoryFactory->create();
$collection = $newCategory->getCollection();
$objectManager->get('Magento\Framework\Registry')->register('isSecureArea', true);

foreach($collection as $category) {

    $category_id = $category->getId();

    if( $category_id <= 2 ) continue;

    try {
        $category->delete();
        echo 'Category Removed '.$category_id .PHP_EOL;
    } catch (Exception $e) {
        echo 'Failed to remove category '.$category_id .PHP_EOL;
        echo $e->getMessage() . "\n" .PHP_EOL;
    }
}

}

Please click for detailed explanation. http://www.pearlbells.co.uk/mass-delete-magento-2-categories-programmatically/

No, it won't reset it will start from n+1 ( n is the last category id in the previous version ) — Liz Eipe C, Commented Jul 2, 2018 at 8:14

Yamen Ashraf · Accepted Answer · 2018-04-26 09:34:56Z

Imporving @Liz answer

public function deleteStoreCategories()
{
    $objectManager = Magento\Framework\App\ObjectManager::getInstance();
    $categoryFactory = $objectManager->get('Magento\Catalog\Model\CategoryFactory');
    $newCategory = $categoryFactory->create();
    $collection = $newCategory->getCollection();
    $objectManager->get('Magento\Framework\Registry')->register('isSecureArea', true);

    foreach ($collection as $category) {
        $category_id = $category->getId();

        if ($category_id <= 2) {
            continue;
        }

        try {
            $category->delete();
            echo 'Category Removed ' . $category_id . PHP_EOL;
        } catch (\Exception $e) {
            echo 'Failed to remove category ' . $category_id . PHP_EOL;
            echo $e->getMessage() . "\n" . PHP_EOL;
        }
    }
}

Hasenpriester · Accepted Answer · 2018-06-21 19:17:40Z

3

(works with Magento 2.2)

If trigger and foreign keys work properly, what should be the case, then this is how it works:

DELETE FROM `catalog_category_entity` WHERE `entity_id` >= 3;
ALTER TABLE `catalog_category_entity` AUTO_INCREMENT = 3;

answered Jun 21, 2018 at 19:17

Hasenpriester

1411 bronze badge

Add a comment |

shreyas d · Accepted Answer · 2020-05-07 10:56:37Z

I have been using this recently with apparent success on Magento 2.1:

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`,`created_at`,`updated_at`,`attribute_set_id`,`parent_id`,`path`,`POSITION`,`level`,`children_count`) 
    VALUES  (1,'2020-05-06 17:27:35','2020-05-06 17:27:35',0,0,'1',0,0,1),
            (2,'2020-05-06 17:27:35','2020-05-06 17:27:35',3,1,'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'),
            (3,52,0,2,'PRODUCTS');
SET FOREIGN_KEY_CHECKS = 1;
DELETE FROM url_rewrite WHERE entity_type = 'category';

The INSERT statements are based on the contents of a completely clean installed Magento 2.

Sachin · Accepted Answer · 2018-12-26 12:39:47Z

Magento EE 2.2.6

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;

TRUNCATE TABLE `sequence_catalog_category`;

TRUNCATE TABLE `visual_merchandiser_rule`;

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

INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `row_id`, `value`) VALUES 
('1', '70', '0', '1', '1'),
('2', '47', '0', '2', '1'),
('3', '70', '0', '2', '1');

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

INSERT INTO `sequence_catalog_category`(`sequence_value`) VALUES 
('1'),
('2');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite WHERE entity_type = 'category';

moazzams · Accepted Answer · 2021-06-10 14:54:45Z

Delete the Specfic category on magetnto 2 from this script

<?php
use Magento\Framework\App\Bootstrap;
include('./app/bootstrap.php');
$bootstrap = Bootstrap::create(BP, $_SERVER);

$objectManager = Magento\Framework\App\ObjectManager::getInstance();
deleteAllCategories($objectManager);

function deleteAllCategories($objectManager) {

$categoryFactory = $objectManager- 
>get('Magento\Catalog\Model\CategoryFactory');

// $specificIds=46;

// $loadCategory=$newCategory->load('42');
// $collection = $newCategory->getCollection();
//put the categories ids
$specificIds=array('51','53','54','55');
$objectManager->get('Magento\Framework\Registry')- 
 >register('isSecureArea', true);
foreach($specificIds as $category) {
    $category_id = $categoryFactory->create()->load($category);
    if( $category <= 2 ) 
        continue;
    try {
        $category_id->delete();
        echo 'Category Removed '.$category_id->getId() .PHP_EOL;
    } catch (Exception $e) {
        echo 'Failed to remove category '.$category_id->getId() .PHP_EOL;
        echo $e->getMessage() . "\n" .PHP_EOL;
    }
}

}

Marcin Dykas · Accepted Answer · 2022-06-13 16:33:22Z

To delete single category categoryRepository should be used.

use Magento\Catalog\Model\CategoryRepository;

    /**
     * @var CategoryRepository
     */
    private $categoryRepository;

    public function __construct(
        CategoryRepository $categoryRepository,
        Registry $registry
    ) {
        $this->categoryRepository = $categoryRepository;
        $this->registry = $registry;
    }

$this->registry->register("isSecureArea", true);

$this->categoryRepository->deleteByIdentifier(666);

Funny fact is that register isSecureArea is needed even when register object is deprecated...

Stack Exchange Network

Magento 2: How to delete all categories at once?

9 Answers 9

Your Answer

Not the answer you're looking for? Browse other questions tagged
magento2
database
category
sql
or ask your own question.

Linked

Hot Network Questions

Magento 2: How to delete all categories at once?

9 Answers 9

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged magento2databasecategorysql or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
magento2
database
category
sql
or ask your own question.