magento 2 query to delete categories












1














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.






magento-2.1 database sql







share|improve this question
























  • 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 '18 at 17:04
















1














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.






magento-2.1 database sql







share|improve this question
























  • 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 '18 at 17:04














1












1








1







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.






magento-2.1 database sql







share|improve this 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.






magento-2.1 database sql




magento-2.1 database sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited yesterday









Teja Bhagavan Kollepara

2,93841847




2,93841847










asked Mar 31 '17 at 8:21









characterError

2319




2319












  • 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 '18 at 17:04


















  • 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 '18 at 17:04
















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 '18 at 17:04




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 '18 at 17:04










1 Answer
1






active

oldest

votes


















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





share|improve this answer























  • But i don't want to eliminate them all, only one (or those who fulfill the condition)
    – characterError
    Mar 31 '17 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?
    – characterError
    Apr 5 '17 at 7:29










  • May be. Please check all values which you passing in query @characterError
    – Mukesh Prajapati
    Apr 5 '17 at 13:06










  • It let an emty name to "default category" whatever i change
    – characterError
    Apr 6 '17 at 11:22











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "479"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f167090%2fmagento-2-query-to-delete-categories%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









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





share|improve this answer























  • But i don't want to eliminate them all, only one (or those who fulfill the condition)
    – characterError
    Mar 31 '17 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?
    – characterError
    Apr 5 '17 at 7:29










  • May be. Please check all values which you passing in query @characterError
    – Mukesh Prajapati
    Apr 5 '17 at 13:06










  • It let an emty name to "default category" whatever i change
    – characterError
    Apr 6 '17 at 11:22
















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





share|improve this answer























  • But i don't want to eliminate them all, only one (or those who fulfill the condition)
    – characterError
    Mar 31 '17 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?
    – characterError
    Apr 5 '17 at 7:29










  • May be. Please check all values which you passing in query @characterError
    – Mukesh Prajapati
    Apr 5 '17 at 13:06










  • It let an emty name to "default category" whatever i change
    – characterError
    Apr 6 '17 at 11:22














1












1








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





share|improve this answer














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






share|improve this answer














share|improve this answer



share|improve this answer








edited May 16 '17 at 7:25









Teja Bhagavan Kollepara

2,93841847




2,93841847










answered Mar 31 '17 at 8:38









Mukesh Prajapati

1,140415




1,140415












  • But i don't want to eliminate them all, only one (or those who fulfill the condition)
    – characterError
    Mar 31 '17 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?
    – characterError
    Apr 5 '17 at 7:29










  • May be. Please check all values which you passing in query @characterError
    – Mukesh Prajapati
    Apr 5 '17 at 13:06










  • It let an emty name to "default category" whatever i change
    – characterError
    Apr 6 '17 at 11:22


















  • But i don't want to eliminate them all, only one (or those who fulfill the condition)
    – characterError
    Mar 31 '17 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?
    – characterError
    Apr 5 '17 at 7:29










  • May be. Please check all values which you passing in query @characterError
    – Mukesh Prajapati
    Apr 5 '17 at 13:06










  • It let an emty name to "default category" whatever i change
    – characterError
    Apr 6 '17 at 11:22
















But i don't want to eliminate them all, only one (or those who fulfill the condition)
– characterError
Mar 31 '17 at 8:54




But i don't want to eliminate them all, only one (or those who fulfill the condition)
– characterError
Mar 31 '17 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?
– characterError
Apr 5 '17 at 7:29




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?
– characterError
Apr 5 '17 at 7:29












May be. Please check all values which you passing in query @characterError
– Mukesh Prajapati
Apr 5 '17 at 13:06




May be. Please check all values which you passing in query @characterError
– Mukesh Prajapati
Apr 5 '17 at 13:06












It let an emty name to "default category" whatever i change
– characterError
Apr 6 '17 at 11:22




It let an emty name to "default category" whatever i change
– characterError
Apr 6 '17 at 11:22


















draft saved

draft discarded




















































Thanks for contributing an answer to Magento Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f167090%2fmagento-2-query-to-delete-categories%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

1300-talet

Image
Den här artikeln handlar om hundraårsperioden 1300–1399. För decenniet 1300–1309, se 1300-talet (decennium). .mw-parser-output .infobox{border:1px solid #aaa;background-color:#f9f9f9;color:black;margin:.5em 0 .5em 1em;padding:.2em;float:right;clear:right;width:22em;text-align:left;font-size:88%;line-height:1.6em}.mw-parser-output .infobox td,.mw-parser-output .infobox th{vertical-align:top;padding:0 .2em}.mw-parser-output .infobox caption{font-size:larger}.mw-parser-output .infobox.bordered{border-collapse:collapse}.mw-parser-output .infobox.bordered td,.mw-parser-output .infobox.bordered th{border:1px solid #aaa}.mw-parser-output .infobox.bordered .borderless td,.mw-parser-output .infobox.bordered .borderless th{border:0}.mw-parser-output .infobox-showbutton .mw-collapsible-text{color:inherit}.mw-parser-output .infobox.bordered .mergedtoprow td,.mw-parser-output .infobox.bordered .mergedtoprow th{border:0;border-top:1px solid #aaa;border-right:1px solid #aaa}.mw-parser-output ...
Read more

1300-talet

Image
Den här artikeln handlar om hundraårsperioden 1300–1399. För decenniet 1300–1309, se 1300-talet (decennium). .mw-parser-output .infobox{border:1px solid #aaa;background-color:#f9f9f9;color:black;margin:.5em 0 .5em 1em;padding:.2em;float:right;clear:right;width:22em;text-align:left;font-size:88%;line-height:1.6em}.mw-parser-output .infobox td,.mw-parser-output .infobox th{vertical-align:top;padding:0 .2em}.mw-parser-output .infobox caption{font-size:larger}.mw-parser-output .infobox.bordered{border-collapse:collapse}.mw-parser-output .infobox.bordered td,.mw-parser-output .infobox.bordered th{border:1px solid #aaa}.mw-parser-output .infobox.bordered .borderless td,.mw-parser-output .infobox.bordered .borderless th{border:0}.mw-parser-output .infobox-showbutton .mw-collapsible-text{color:inherit}.mw-parser-output .infobox.bordered .mergedtoprow td,.mw-parser-output .infobox.bordered .mergedtoprow th{border:0;border-top:1px solid #aaa;border-right:1px solid #aaa}.mw-parser-output ...
Read more

Display a custom attribute below product name in the front-end Magento 1.9.3.8

Image
0 I need to display a custom attribute bellow the product name and I don't know how can I call this attribute. I am editing the file /app/design/frontend/templatemela/MAG100219_1/template/catalog/product/view.phtml But how can I call this attribute in php? I tried calling a string echo with the name I put in the attribute, but it didn't work. Code is as below: <div class="page-title product-title"> <h1><?php echo $_helper->productAttribute($_product, $_product->getName(), 'name') ?></h1> </div> <div class="codigoinmetro"> <h2><?php echo $_helper->productAttribute($_product, $_product->getName(), 'registroinmetro') ?></h2> </div> php attrib...
Read more