Magento 2.3 stock update in DB level

Question

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

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);
    $stockItem->setQty($qty);
    $stockItem->setIsInStock((bool)$qty);
    $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!