Gsyfuy

Output visual diagram of picture

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

Checking @@ROWCOUNT failing

Why can't I get pgrep output right to variable on bash script?

Does capillary rise violate hydrostatic paradox?

Center page as a whole without centering each element individually

Error in master's thesis, I do not know what to do

Reason why a kingside attack is not justified

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

Pre-Employment Background Check With Consent For Future Checks

Why didn't Voldemort know what Grindelwald looked like?

How to test the sharpness of a knife?

How to preserve electronics (computers, ipads, phones) for hundreds of years?

How do you say "Trust your struggle." in French?

Are all namekians brothers?

Weird lines in Microsoft Word

Capacitor electron flow

What is this high flying aircraft over Pennsylvania?

A seasonal riddle

Started in 1987 vs. Starting in 1987

Not hide and seek

How do I lift the insulation blower into the attic?

What is the meaning of "You've never met a graph you didn't like?"

Is divisi notation needed for brass or woodwind in an orchestra?

Query Magento2 database to find all SKUs without managed stock set to yes

Changing page layout through a database tableCredit memo return to stock adds a quantity to configurable and simpleWhere does Magento store the PRODUCT_HAS_WEIGHT attributeMove out of stock product in category view last if qty is 0Magento 2 UPSELL product list showing same price as of ProductMagento 2.2 - Product always in stock?Magento CE 1.9 Script for setting stockcataloginventory_stock_status entry issue Magento 2How to update product stock via CSV (Magento 2.x)How to manage stock in multi store website in Magento 2?

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

share|improve this question

edited 7 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

share|improve this question

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

add a comment | 

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

share|improve this question

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

share|improve this question

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

share|improve this question

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

edited 7 mins ago

Teja Bhagavan Kollepara

3,00641949

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

asked Aug 17 '18 at 13:41

robgtrobgt

2841417

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

share|improve this answer

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: 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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

Name

Email

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

Name

Email

Required, but never shown

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

share|improve this answer

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: 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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

share|improve this answer

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: 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 | 

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

share|improve this answer

answered Aug 17 '18 at 14:00

Sukumar GoraiSukumar Gorai

6,8853729

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

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

share|improve this answer

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