Bulk Update Multiple Records with Separate Data — Laravel
Tags: laravel, mysql
Published: 2021-07-11 23:45:04

As a rule of thumb, we should never run database queries inside a for-loop!

**“Database transaction” is an expensive operation.**

For example, let’s say we designed an inventory software and it’s being used in production for a year, and reached 1,000,000 transactions.

Suddenly, we learnt that we didn’t add the VAT to our transactions. For the transactions in the future, it’s pretty easy to deal with, maybe with a mutator.

```
class Transaction extends Model { 
    public $vat = 0.20;

    public function setPriceAttribute($value) {
        $this->attributes['price'] += $value * $this->vat;
    }
}
```

Future records are pretty easy to deal with. However how are we going to edit the 1 million records from the past.

For editing data from the past, I prefer to create a Seeder.

`php artisan make:seeder AddVatToTransactions`

#### How not to do it?

![Drake says no](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z4zndxs8ysb5s7fbw5r4.jpeg)

```
class AddVatToTransactions extends Seeder {

  public function run() 
  {
    $vat = 0.20;
    $transactions = Transaction::get();

    foreach ($transactions as $transaction) {
       $transaction->price += $transaction->price * $vat
       $transaction->save();
    }
  }
}
```

However, running it in a loop of 1 million and making a “database transaction” in each iteration of the loop — not a good idea! (Spoiler Alert: It’ll freeze your system 😀)

----

#### Then, how to do it?

Again, in our `AddVatToTransactions` Seeder:

The idea in mysql query is “CASE Statements”
```
UPDATE db.transactions
SET PRICE = CASE  
              WHEN id = 3 THEN 500
              WHEN id = 4 THEN 300
           END 
WHERE ID IN (3, 4)
```

#### Now, let’s do it in Laravel:
```
$vat = 0.20;
$transactions = Transaction::get();

$cases = [];
$ids = [];
$params = [];

foreach ($transactions as $transaction) {
    $cases[] = "WHEN {$transaction->id} then ?";
    $params[] = $transaction->profit * $vat;
    $ids[] = $transaction->id;
}

$ids = implode(',', $ids);
$cases = implode(' ', $cases);

if (!empty($ids)) {
    \DB::update("UPDATE transactions SET `price` = CASE `id` {$cases} END WHERE `id` in ({$ids})", $params);
}
```

This will make one database transaction to write it all your updates.⚡️

![Drake says yes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iq7j5qnkh4ejehqx0lme.jpeg)

-----

🗣 I can hear some of you saying: “It’s still FREEZING”

### So.. Optimizing it even further:

**#1: “Select” only the data you need from the database to consume less RAM.**

In our example, we only use “id” and “price” columns. So let’s only select them.

```
$transactions = Transaction::select('id', 'price')->get();
```

**#2: “Chunk” your collection to separate your transaction to multiple database transactions.**

In Laravel, you can chunk collections like

```
Transaction::get()->chunk(5000);
```

----

### Let’s apply all in our example

Here, first we divide our `$transactions` collection into 5000 chunks and we do a “database transaction” per 5k records at once.

```
$vat = 0.20;
$transactions = Transaction::get();

foreach ($transactions->chunk(5000) as $chunk) {
   $cases = [];
   $ids = [];
   $params = [];
   
   foreach ($chunk as $transaction) {
       $cases[] = "WHEN {$transaction->id} then ?";
       $params[] = $transaction->profit * $vat;
       $ids[] = $transaction->id;
   }

   $ids = implode(',', $ids);
   $cases = implode(' ', $cases);

   if (!empty($ids)) {
       \DB::update("UPDATE transactions SET `price` = CASE `id` {$cases} END WHERE `id` in ({$ids})", $params);
   }
}
```

Hope you like this trick!

Please let me know what you think in the comments 💬

Happy coding! 😊