Query Magento2 database to find all SKUs without managed stock set to yes
1
Is there a database query that I can run against my Magento 2.1.6 database to find all SKUs that have Manage Stock set to No.
I need to have ALL products set to Yes and Use Default checkbox ticked.
If there's only a handful, I can update them manually, but if there's a lot, I would prefer to update the database instead.
database magento2.1.6 manage-stock
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
add a comment |
1
Is there a database query that I can run against my Magento 2.1.6 database to find all SKUs that have Manage Stock set to No.
I need to have ALL products set to Yes and Use Default checkbox ticked.
If there's only a handful, I can update them manually, but if there's a lot, I would prefer to update the database instead.
database magento2.1.6 manage-stock
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
add a comment |
1
1
1
Is there a database query that I can run against my Magento 2.1.6 database to find all SKUs that have Manage Stock set to No.
I need to have ALL products set to Yes and Use Default checkbox ticked.
If there's only a handful, I can update them manually, but if there's a lot, I would prefer to update the database instead.
database magento2.1.6 manage-stock
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
Is there a database query that I can run against my Magento 2.1.6 database to find all SKUs that have Manage Stock set to No.
I need to have ALL products set to Yes and Use Default checkbox ticked.
If there's only a handful, I can update them manually, but if there's a lot, I would prefer to update the database instead.
database magento2.1.6 manage-stock
database magento2.1.6 manage-stock
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
edited 3 mins ago
Teja Bhagavan Kollepara
3,00641949
3,00641949
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
asked Aug 17 '18 at 13:41
robgtrobgt
2841417
2841417
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
If you want to get all the skus with manage_stock then you can run the following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
And if you want to filter those with manage stock no then use following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 where cataloginventory_stock_item.manage_stock = 0
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECTe.sku,csi.manage_stockFROMcatalog_product_entityASeLEFT JOINcataloginventory_stock_itemAScsiON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...
– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f238755%2fquery-magento2-database-to-find-all-skus-without-managed-stock-set-to-yes%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
0
If you want to get all the skus with manage_stock then you can run the following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
And if you want to filter those with manage stock no then use following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 where cataloginventory_stock_item.manage_stock = 0
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECTe.sku,csi.manage_stockFROMcatalog_product_entityASeLEFT JOINcataloginventory_stock_itemAScsiON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...
– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
add a comment |
0
If you want to get all the skus with manage_stock then you can run the following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
And if you want to filter those with manage stock no then use following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 where cataloginventory_stock_item.manage_stock = 0
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECTe.sku,csi.manage_stockFROMcatalog_product_entityASeLEFT JOINcataloginventory_stock_itemAScsiON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...
– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
add a comment |
0
0
0
If you want to get all the skus with manage_stock then you can run the following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
And if you want to filter those with manage stock no then use following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 where cataloginventory_stock_item.manage_stock = 0
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
If you want to get all the skus with manage_stock then you can run the following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
And if you want to filter those with manage stock no then use following query:
SELECT `e`.sku, `cataloginventory_stock_item`.`manage_stock` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `cataloginventory_stock_item` ON (cataloginventory_stock_item.product_id=e.entity_id) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 where cataloginventory_stock_item.manage_stock = 0
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
answered Aug 17 '18 at 14:00
Sukumar GoraiSukumar Gorai
6,8853729
6,8853729
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECTe.sku,csi.manage_stockFROMcatalog_product_entityASeLEFT JOINcataloginventory_stock_itemAScsiON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...
– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
add a comment |
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECTe.sku,csi.manage_stockFROMcatalog_product_entityASeLEFT JOINcataloginventory_stock_itemAScsiON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...
– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECT
e.sku, csi.manage_stock FROM catalog_product_entity AS e LEFT JOIN cataloginventory_stock_item AS csi ON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...– robgt
Aug 29 '18 at 13:08
Thanks for the SQL! I have run this after making some modifications for things not needed in there, but my results are very confusing. New SQL is: SELECT
e.sku, csi.manage_stock FROM catalog_product_entity AS e LEFT JOIN cataloginventory_stock_item AS csi ON (csi.product_id=e.entity_id) WHERE csi.manage_stock = 0 But when I check magento admin for one of the returned products, the manage stock field is set to yes, while my results from sql tell me it should be set to no!!?? Very confusing what's going on here...– robgt
Aug 29 '18 at 13:08
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
It should work. Its working fine in my local system.
– Sukumar Gorai
Aug 29 '18 at 13:28
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
Agreed - it makes zero sense to me. I can't imagine the data shown to me in my admin is being pulled from somewhere else, but the query returns different data. Definitely from the correct database, so I am lost :-(
– robgt
Aug 31 '18 at 11:54
add a comment |
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.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f238755%2fquery-magento2-database-to-find-all-skus-without-managed-stock-set-to-yes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
