Laravel Bulk Update for multiple record ids

Question

I want to mass update my records in Laravel but the records not getting updated. I have a different record for each Id. Below is what I am trying.

$ids = [5,6,8,9],
$updated_array = [
  ['name' => 'tarun'],
  ['name' => 'Akash'],
  ['name' => 'Soniya'],
  ['name' => 'Shalu'],
];

Model::whereIn('id', $ids)->update($updated_array);

Answer 1

Mass updates are used when you're trying to update multiple rows to the same values. You cannot mass update with different values.

Therefore, this would work, but would update all matching records to name of 'tarun':

Model::whereIn('id', $ids)->update(['name' => 'tarun']);

For your example, you could do:

foreach($ids as $key => $id) {
    Model::where('id', $id)->update($updated_array[$key]);
}

But as far as I know, there's no way to do this without running 4 queries in Laravel and writing a raw SQL statement to accomplish this would even be messy.

Answer 2

Some good solutions for this issue are on the following post: https://github.com/laravel/ideas/issues/575

1) Can create custom function to do this, which uses raw SQL, as per barryvdh comment in the post.

    public static function updateValues(array $values)
    {
        $table = MyModel::getModel()->getTable();

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

        foreach ($values as $id => $value) {
            $id = (int) $id;
            $cases[] = "WHEN {$id} then ?";
            $params[] = $value;
            $ids[] = $id;
        }

        $ids = implode(',', $ids);
        $cases = implode(' ', $cases);
        $params[] = Carbon::now();

        return \DB::update("UPDATE `{$table}` SET `value` = CASE `id` {$cases} END, `updated_at` = ? WHERE `id` in ({$ids})", $params);
    }

This apparently increase performance by 13x

2) As per another comment, another idea is to do it on a per record basis, but do the whole thing as a single transaction, which makes it much faster.

    DB::beginTransaction();

    // your loop and updates;

    if( !$user )
    {
    rollbackTransaction();
    } else {
    // Else commit the queries
    commitTransaction();
    }

3) There is a laravel library also that appears to try and solve this issue. https://github.com/mavinoo/laravelBatch

Note: I have not tried or tested any of the above solutions.

Answer 3

You can use Laravel Upsert for mass update. For example :

User::query()->upsert([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
], 'email');

This feature available in Laravel 8 or newer

Answer 4

I think the only way to do this without n queries would be to

• (optional) backup table
• grab the records and create an array with all the updated data
• bulk delete the records
• bulk insert from array

That's 3 queries.

Iterating through n records is not practical for my application either; I was hoping there was an alternative but it looks like I'm going to have to implement this.