magento 2 query to delete categories

Question

I want a query to delete one category and all the associate values in all the tables. In specific i need a query to delete all categories where value=null.

I tried it by code, but it doesn't work.

Answer 1

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

Answer 2

Thinking outside the box

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM catalog_category_entity WHERE entity_id = [whatever];

Then you will have to delete orphan data with queries like this

DELETE FROM catalog_category_entity_varchar WHERE entity_id NOT IN (SELECT entity_id FROM catalog_category_entity)

Plus all the other tables mentioned in other answer.

SET FOREIGN_KEY_CHECKS = 1;

Once done