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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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