Update query in bulk using single query instead of using loop

Hello all,

I'm looking for a query to update in bulk using single query instead of using query in loop. I have more than 2000 products and I have to update there stock value. Right now I using loop for each product so its take too much time. Here is my code

foreach($products as $srcProduct){ 
                $product_id = $srcProduct['product_id'];         
                $onhand = 0;
                if(isset($srcProductOnhand[$product_id])){
                        $onhand = $srcProductOnhand[$product_id];
                 }
TransferSourceProducts::where(['product_id' => $product_id, 'transfer_id' => $id])
                        ->update(['onhand' =>$onhand]); 
                  
 }

nionta

6 years ago

Level 1

try this:

        TransferSourceProducts::whereIn('product_id' , $product_id) ->where('transfer_id' , $id)
                    ->update(['onhand' =>$onhand]);

if all of your product ids are in an array; it would be great if you can show, from where you are getting your product ids

1 like

rocketbg

6 years ago

Level 16

A way to do this is to collect all (product_id - transfer_id)'s as a concatenated strings and then make a ->update query to all TransferSourceProducts whereIn -> you create a virtual/calculated column of the concatenated product_id , transfer_id fields (via DB:raw and Concat(product_id, '-', transfer_id)). This is if you use eloquent but better if you do it in just a Native Query (it's much simpler).

V9द

6 years ago

Level 3

@nionta thanks for reply, Here on hand value is dynamic for every product. Its not same for every product.

rocketbg

6 years ago

Level 16

This is achievable only if you create a stored procedure in your SQL server and call it from your PHP providing the prepared structure of data (product - transfer - onhand_value). That way you can update the SQL rows with only one call to your SQL server.

Robstar

6 years ago

Level 50

For that many records you may want to investigate generators and possibly lazy collections (if using Laravel 6).

I assume $srcProductOnhand is a huge array of ids?

Please or to participate in this conversation.