Update product stock directly from Magento database

Home / Magento / Update product stock directly from Magento database

Sometime in some situation development time we need to update products quantity and stock status directly in the magento database. here I describe the most efficient way to do this is to update the products quantity and stock status directly in the database.

Product quantity is stored in cataloginventory_stock_item table in qty and stock status in is_in_stock column, also we have to update cataloginventory_stock_status, the columns qty and stock_status with the corresponding Boolean value. So, the SQL query should look something like below query:

UPDATE cataloginventory_stock_item item_stock, cataloginventory_stock_status status_stock
SET item_stock.qty = '$new_quantity', item_stock.is_in_stock = IF('$new_quantity'>0, 1,0),
status_stock.qty = '$new_quantity', status_stock.stock_status = IF('$new_quantity'>0, 1,0)
WHERE item_stock.product_id = '$product_id' AND item_stock.product_id = status_stock.product_id

where $new_quantity contains the product quantity sent by our ERP system and $product_id is the corresponding product entity id sent by our ERP.
Aright. below is our code to update the stocks:

private function _updateProductQty($database, $product_id, $new_quantity) {
$database->query ( "UPDATE cataloginventory_stock_item item_stock, cataloginventory_stock_status status_stock
   SET item_stock.qty = '$new_quantity', item_stock.is_in_stock = IF('$new_quantity'>0, 1,0),
   status_stock.qty = '$new_quantity', status_stock.stock_status = IF('$new_quantity'>0, 1,0)
   WHERE item_stock.product_id = '$product_id' AND item_stock.product_id = status_stock.product_id " );
}

how we use this method?

$coreResource = Mage::getSingleton ( 'core/resource' );
$write = $coreResource->getConnection ( 'core_write' );
$this->_updateProductQty ( $write, $product_id, $new_quantity );

Hope you enjoyed or helpful to you with this information.

Related Posts

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *