How to update multiple rows of a table in a single query?

Question

I need to update the qty of all products (around 15.000 items) in a cycle, but it takes a very long time. How can I build a SQL script within the loop to run it in a single execution?. I have this code:

This takes more than 40 minutes:

$products_from_erp;//array with product data from ERP system
foreach($products_from_erp as $data_product){
  $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($data_product['product_id']);
  $stockItem->setQty($data_product['qty']);
  $stockItem->setIsInStock(($data_product['qty'] > 0) ? 1 : 0);
  $stockItem->save();
}

This takes 15 minutes:

$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
foreach($products_from_erp as $data_product){
  $bind = array(
      'qty' => $data_product['qty'],
      'is_in_stock' => ($data_product['qty'] > 0) ? 1 : 0
  );
  $where = 'product_id = '.$data_product['product_id'];
  $connection->update('cataloginventory_stock_item',$bind,$where);
}

I also tried this code (It should be much faster) but it takes 15 minutes approx:

$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = "";
foreach($products_from_erp as $data_product){
  $qty = $data_product['qty'];
  $is_in_stock = ($data_product['qty'] > 0) ? 1 : 0;
  $product_id = $data_product['product_id'];
  $sql .= "UPDATE cataloginventory_stock_item SET qty = '$qty', is_in_stock = '$is_in_stock' WHERE product_id = $product_id; ";
}
$connection->query($sql);

Answer 1

Your best bet is likely to use an insert query with the ON DUPLICATE KEY UPDATE command, like this:

INSERT INTO `cataloginventory_stock_item` (item_id, qty, is_in_stock) VALUES
    (1, 1, 1),
    /*One for each product...*/
    ON DUPLICATE KEY UPDATE
        SET qty=VALUES(qty);

Basically, that's the query you want to build and run. Note that this will work when all the products already exist, otherwise you'll have to figure out a solution with the AUTO_INCREMENT value of the table.

To build the query, you need to replace the (1, 1, 1) in my above example with data for each product. The first number will be the item ID for the stock item, and the second will be the new qty. The third will be the is_in_stock value.

I suggest walking the array to figure out the values for each product. You'll put that on a new line for each one in it's own set of parentheses.

One other challenge you'll run into is likely having to retrieve the item_id for each stock item; I don't think this will work unless you use the actual item id, not the product ID, although it might because it is unique, but I'm not that much of a DB expert. If it doesn't work with the product key (remember to swap out the column name in the first part), then you'll have to run a second query to select all item ID's and product ID's, and then some additional processing. That should slow things down a little, but not too much.

Please let me know if you have any questions! I realize that this is super complicated.