Magento SQL – Set Manage Stock 1 for All Simple Products

MySQLstock

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.

Best Answer

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')

Related Solutions

Magento – Magento – foreach simple product show name, sku and stock quantity

This should work to quickly grab everything you want. There may be a better way to do it.

<?php 
// Load Magento
require_once '../app/Mage.php';
umask(0);
Mage::app('default');


//get the collection filter the simple products & join the cataloginventory/stock_item table
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('sku')->addAttributeToFilter('type_id', 'simple')->joinField(
    'qty',
    'cataloginventory/stock_item',
    'qty',
    'product_id=entity_id',
    '{{table}}.stock_id=1',
    'left'
) ;
?>
<html>
<head></head>
<body>
<table>
<?php 
foreach ($collection as $product) {
    echo "<tr><td>".$product->getSku()."</td><td> ".(int)$product->getQty()."</td></tr>";
};




?>
</table>
</body></html>

Magento – Change Simple Products to Manage Stock = No

I know that this doesn't dirrectly answer your question, as it doesn't do this via an SQL statement, however, you could achieve this programatically.

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToFilter('type_id', array('eq' => 'simple'));

foreach ($collection as $product) {
     $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product->getId());
     $stockItem->setData('manage_stock', 0);
     $stockItem->setData('use_config_manage_stock', 0);
     $stockItem->save();
}

I don't think there is a way to grab only simple products that aren't associated with configurable products, unless you can filter by another attribute; for example, visibility (if associated products are not visible - only through their parent). The alternative would be to load all the configurable products, grab all the associated product IDs - getUsedProductIds() and then check if each simple product is in the array or not.

Best Answer

Related Solutions

Magento – Magento – foreach simple product show name, sku and stock quantity

Magento – Change Simple Products to Manage Stock = No

Related Topic