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

An IMO inspired problem

Image
14 4 This problem from IMO 1988 is said to be one of the most elegant ones in functional equations. Problem : The function $f$ is defined on the set of all positive integers as follows: begin{align} f(1) = 1, f(3) &= 3, f(2n) = f(n), \ f(4n+1) &= 2f(2n+1) - f(n), \ f(4n+3) &= 3 f(2n+1) - 2f(n) end{align} Find the number of $n$ with $f(n) = n, 1 leq n leq 1988$. The main idea towards the solution is realizing that these conditions stand for the fact that $f(n)$ just reverses the digits in the binary representation of the number $n$. So, essentially the solution is finding the number of binary pallindromes $leq 1988_{10}$. My question is the following : How to reformulate the problem so that $f(n)$ reverses the digits of $n$ in its ternary representation. Or even better, can we reformulate it for a
Read more

Management

Image
Management From Wikipedia, the free encyclopedia Jump to navigation Jump to search "Manager" redirects here. For other uses, see Management (disambiguation) and Manager (disambiguation). An organization chart for the United States Coast Guard shows the hierarchy of managerial roles in that organization. Business administration Management of a business Accounting Management accounting Financial accounting Financial audit Business entities Cooperative Corporation Limited liability company Partnership Sole proprietorship State-owned enterprise Corporate governance Annual general meeting Board of directors Supervisory board Advisory board Audit committee Corporate law Commercial law Constitutional documents Contract Corporate crime Corporate liability Insolvency law International trade law Mergers and acquisitions Economics Commodity Pu
Read more

Has there ever been an instance of an active nuclear power plant within or near a war zone?

Image
36 3 The question Has there ever been a military combat going on around an active (running or in outage but not decommissioned) commercial nuclear power plant? Or dangerously near it? If yes, how did the plant and its personnel fare in such situation? Why I ask Let's assume for the scope of this question modern commercial nuclear power plants (CANDU, PWR, BWR of generation II plus all of gen III and newer) are usually reasonably safe to operate given that the engineering has been done right and there is a whole cohort of very well trained personnel on-site at all times, well rested and with considerable resources on their hands. Things hard to get with an armed conflict like the recent Syrian war raging around. I can't seem to find any reference or a comprehensive article about this, hence this question.
Read more