Magento - update all products inventory with sql

Question

I want to update the inventory of all My Magento products.

Can I do that with an only sql request?

What's the request if that's possible ?

Thanks a lot

Answer 1

Something like this?

UPDATE cataloginventory_stock_item SET qty='<my_quantity>';

Other useful fields in this table could be:

• qty
• min_qty
• use_config_min_qty
• is_qty_decimal
• backorders
• use_config_backorders
• min_sale_qty
• use_config_min_sale_qty
• max_sale_qty
• use_config_max_sale_qty
• is_in_stock
• low_stock_date
• notify_stock_qty
• use_config_notify_stock_qty
• manage_stock
• use_config_manage_stock
• stock_status_changed_automatically
• use_config_qty_increments
• qty_increments
• use_config_enable_qty_increments
• enable_qty_increments

Answer 2

You can update your inventory with sql, but because of the EAV model of Magento, the sql can be a little cumbersome - and you really need to ask yourself why you want to do this. In our case, we have a brick-and-mortar store, so the Magento isn't our primary inventory storage location.

We first extract the UPC, cost, price, and quantity from our store POS database and convert the data into sql insert statements to insert the data into a temporary table in our Magento db:

CREATE TABLE Temp_Inventory (
`UPC` varchar(40) NOT NULL,
`ItemName` varchar(60) NOT NULL,
`Cost` float NOT NULL,
`Price` float NOT NULL,
`In_Stock` float NOT NULL,
`Helper_ItemNum` varchar(40) DEFAULT NULL,
 UNIQUE KEY `Temp_Inventory_IN` (`UPC`),
 KEY `Temp_Inventory_H` (`Helper_ItemNum`));

delete from Temp_Inventory;
insert into Temp_Inventory (UPC, ItemName, Cost, Price, In_Stock) values ("132456789123", "Item Description", 9.6667, 14.9900, 14);

We use UPC as our primary key, but Magento uses its own key, so we've added a 'UPC' attribute (coded 'upc') in the Magento DB, so that we can match items between systems. First we need to populate our temp table with the Magento item id.

 update Temp_Inventory set Helper_ItemNum =
    (select catalog_product_entity.entity_id
    from catalog_product_entity, catalog_product_entity_varchar, eav_attribute
    where eav_attribute.attribute_code = "upc"
    and catalog_product_entity_varchar.entity_type_id = eav_attribute.entity_type_id
    and catalog_product_entity_varchar.attribute_id = eav_attribute.attribute_id
    and catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id
    and catalog_product_entity_varchar.value = UPC);

Then we need to update the inventory values, making sure we only update items that we have values for:

update cataloginventory_stock_item set qty =
    (select In_Stock
    from Temp_Inventory
    where Helper_ItemNum = cataloginventory_stock_item.item_id)
 where cataloginventory_stock_item.item_id in (select Helper_ItemNum from Temp_Inventory);

Cost and price can be updated in a similar manner using the table catalog_product_entity_decimal.

Then we update stock status in-stock vs. out-of-stock:

update cataloginventory_stock_item set is_in_stock = 1
where cataloginventory_stock_item.item_id in (select Helper_ItemNum from Temp_Inventory)
and cataloginventory_stock_item.qty > 0;

update cataloginventory_stock_item set is_in_stock = 0
where cataloginventory_stock_item.item_id in (select Helper_ItemNum from Temp_Inventory)
and cataloginventory_stock_item.qty <= 0;

Finally we need to reindex stock status, so our site shows it correctly:

php -f /path/to/magento/shell/indexer.php -- --reindex cataloginventory_stock

Answer 3

function updateProductStock($productId, $qty) { 
    $resource = Mage::getSingleton('core/resource');
    $write = $resource->getConnection('core_write');
    $write->update(
      "cataloginventory_stock_item"
    , array("qty" => $qty, 'is_in_stock' => ($qty > 0 ? 1 : 0))
    , "product_id = " . $productId
    );
} 

Answer 4

Used within a product foreach loop, you can modify the following code:

$newstocklevel = 100;

$product_id = Mage::getModel('catalog/product')->getIdBySku(321);
$product = Mage::getModel('catalog/product');
$product ->load($product_id);
$stockData = $product->getStockData();
$stockData['qty'] = $newstocklevel;
$stockData['is_in_stock'] = 1;

$product->setStockData($stockData);

$product->save();