2

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.

1
  • the insert lines in Mukesh Prajapati's answer should be updated as follows for newer versions of Magento 2 INSERT INTO catalog_category_entity (entity_id, attribute_set_id, parent_id, created_at, updated_at, path, position, level, children_count, created_in, updated_in) VALUES ('1', '3', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1', '1', '2147483647'), ('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0', '1', '2147483647'); INSERT INTO catalog_category_entity_int (value_id, attribute_id, store_id, row_id, value) VALUES
    – Scott
    Apr 11, 2018 at 17:04

2 Answers 2

3

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

enter image description here

4
  • But i don't want to eliminate them all, only one (or those who fulfill the condition) Mar 31, 2017 at 8:54
  • I tried it (only to try in another magento) and it doesn't create de default root category. Did you know why? maybe because me store_id =1? Apr 5, 2017 at 7:29
  • May be. Please check all values which you passing in query @characterError Apr 5, 2017 at 13:06
  • It let an emty name to "default category" whatever i change Apr 6, 2017 at 11:22
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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