I'm Currently using magento2.3, i have added a cron job to update products stock every 20 min from external inventory which will create a CSV file to magento directory, i will read this file and loop to the quantity and create a SQL query to update it to cataloginventory_stock_item table and run reindex.

Quantity get updated but not showing add to cart in front-end , after product save manually from admin it's working fine. It is proper method to update qty or else there is any other method for performance using this method, because our store having more then 20K+ products

1 Answer 1


Please try the below code if it helps instead of updating the stock directly to the database table.

Use the Magento\CatalogInventory\Api\StockRegistryInterface to update the product qty.

public function updateQty($qty,$sku) {
    $stockItem = $stockRegistry->getStockItemBySku($sku);
    $stockRegistry->updateStockItemBySku($sku, $stockItem);

OR try the below sql query for update stock:

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

Hope this helps!

  • Thanks for you help, As i previously mentioned i'm having 20k+ products, each 20 min cron will run for 20k+ products so performance will affect if i'm using this method ?
    – Diksha R
    Commented May 17, 2020 at 6:02
  • Do you set is_in_stock = 1 ? Like UPDATE cataloginventory_stock_item SET qty = 100, is_in_stock = 1 WHERE product_id = ID. Commented May 17, 2020 at 6:31
  • Yes i am setting is_in_stock = 1 also by checking condition if qty greater than 0 and after reindex cataloginventory_stock_status table also get updated
    – Diksha R
    Commented May 17, 2020 at 6:52
  • Have you tried flushing the cache after the update in database table? Commented May 17, 2020 at 6:58
  • Yes i have but no use
    – Diksha R
    Commented May 17, 2020 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.