Revisions to magento 2 query to delete categories

Add solution for multiple redirection issue after performing mysql sql query.

Source Link

edited Jul 26, 2023 at 9:02

5.3k
4
43
81

Use below queries to delete all categories

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

If you get multiple redirection error while accessing category page after running above query then you need to set default category in default store

Use below queries to delete all categories

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

Use below queries to delete all categories

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

If you get multiple redirection error while accessing category page after running above query then you need to set default category in default store

Edited the content for better view

Source Link

edit approved May 16, 2017 at 7:25

Teja Bhagavan Kollepara

3.8k
5
33
69

useUse below queries to delete all categories

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

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

use below queries to delete all categories

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

Use below queries to delete all categories

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

Source Link

created Mar 31, 2017 at 8:38

Mukesh Prajapati

1.9k
14
22

use below queries to delete all categories

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

Stack Exchange Network

Return to Answer