How to bulk update data using DB query in Laravel 8

Database transactions are sensitive and essential tasks in any type of application development. There might be times when we have updates in a system and need to update an entire database table with thousands of data entries. This requires bulk update data, that can be done in Laravel using the DB query.

Suppose we have an inventory software that is being used for a couple of months and has a record of more than thousands of items. But, after few months we notice, the VAT charges of each product are not applicable. This requires bulk updates in the products database table. For our future entries, we can simply apply the VAT charges and update the total cost variable accordingly.

class Transaction extends Model { 
    public $vat = 0.10;

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

However, to update previous thousands of records, we will have to create a database seeder. Use the command below to add new VAT updates seeder.

php artisan make:seeder UpdateVatToTotalPrice

You can add the code below in your new seeder class.

$vat = 0.10;
$transaction = 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);

The code above loops through all the transactions, and takes in the ids and price parameters in arrays. The active ids are then passed to the DB update query and the price parameter is updated, by adding the VAT percentage to it. This helps you bulk update data that can be up to even millions of records.