Massedit stock status in database

Question

I realized I have a lot of products that have quantity above 0 but are not in stock. So I would like to do a search in my database for products that have quantiy > 0 and stock_status 0, this is the code I am using:

UPDATE `cataloginventory_stock_status` SET `stock_status` = replace(stock_status, '0', '1') WHERE qty > '0.0000' AND stock_status = '0'

While this will find all products and change stock_status in the database, the products stock status will not change. I also updated cataloginventory_stock_status_idx but to no avail. Are there other tables I need to set stock status?

Answer 1

You can do this using Magento code also.

First find all products, which have qty>0 and stock_status=0 and set stock status using code below:

<?php
require_once('app/Mage.php');
umask(0);
Mage::app('admin');
set_time_limit(0);

$productCollection = Mage::getModel('catalog/product')
     ->getCollection()
     ->addAttributeToSelect('*')
     ->joinField('qty',
                 'cataloginventory/stock_item',
                 'qty',
                 'product_id=entity_id',
                 '{{table}}.is_in_stock=0',
                 'left')
     ->addAttributeToFilter('qty', array("gt" => 0));

foreach($productCollection as $product) { //print_r($product->getData());exit;
    $_product = Mage::getModel('catalog/product')->load($product->getId());
    $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product);
    $stockItem->setData('is_in_stock', 1); // is 0 or 1
    $stock_item->setData('manage_stock', 1);
    try {
        $stock_item->save();
    } catch (Exception $e) {
        echo "{$e}";
    }
}
echo 'Done';
?>

Please let me know if you find any problem.