🔥 Bulk Update Multiple Records with Separate Data — Laravel

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

If we had small number of transactions, we could do:

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 ourAddVatToTransactions 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)
$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.

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

#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.

Hope you like this trick!

Please let me know what you think in the comments or simply add a clap if you like it! 😊

Happy coding!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store