I would like to update all my simple products in database by direct sql to set "manage stock" to 1 but don't know how to start.

  • Any specific reason behind using direct sql, you can easily achieve that by import of CSV file having 2 columns only! – Prateek Aug 5 '15 at 19:21
  • Products where imported using third party service which left a lot of them without sku which seems to deny mass update – Stefan Gregori Aug 5 '15 at 19:21

Magento basically mapped products and its inventory to cataloginventory_stock_item .

As you want to update and then you need update the columns at manage_stock, `use_config_manage_stock,is_in_stock.

You can get simple product list from catalog_product_entity

SELECT entity_id FROM catalog_product_entity where type_id='simple'.

Now You can fill your requirement by

UPDATE cataloginventory_stock_item SET is_in_stock=1 , 
use_config_manage_stock =1,is_in_stock=1 where 
product_id in 
(SELECT entity_id FROM catalog_product_entity where type_id='simple')

If you are having products without SKUs then I suggest adding SKUs to the products first, or you are going to have a lot more critcal problems than just "Manage Stock" field. It's a complete recipe for an unstable system you got.

Coming back to question, it's a smallint field called "manage_stock" in cataloginventory_stock_item table in your DB if you don't have table prefix, otherwise 'PREFIX'_cataloginventory_stock_item. You might also want to set "use_config_manage_stock" while in there, so that you can control that for all products from a single location. Don't forget to re-index after this.

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.