Update in bulk - Laravel

Question

I am trying to find someway to update multiple records at once like we have insert for creating multiple records at once.

Lets say I have an associated array which contains data to be updated in each row of a table employees:

$data = [
  ['id'=>1,'name'=>'xxx'],
  ['id'=>2,'name'=>'xxx'],
  ['id'=>3,'name'=>'xxx'],
  ['id'=>4,'name'=>'xxx']
];

One way of updating all these records is:

foreach($data as $d){
   Employee::where('id'=>$d['id'])->update(['name'=>$d['name']]);
}

where Employee is model for employees table ofocurse.

I am wondering if i can update all records by one line statement?

For instance if I had create new records from $data, I would use:

Employee::insert($data);

instead of looping through $data and use create() for each record.

Anything like that exists for updation as well?

Answer 1

No, it's not possible, you should iterate over the array and update each row separately.

Answer 2

Did you try Employee::update($data);? of course data should be as array like: $data = (['name'=>'test'],['surname'=>'test2'])); - it will update all the rows in the table - but you can add an where condition.

Answer 3

Doctrine allows you to do batch inserts/updates. You can find it here: http://www.laraveldoctrine.org/

Answer 4

What about doing something like this, using collections.

$data = [
  ['id'=>3,'name'=>'xxx'],
  ['id'=>4,'name'=>'xxx'],
  ['id'=>5,'name'=>'xxx']
];

Employee::find(collect($data)->pluck('id')->toArray())->map(function($item, $key) use ($data){
  $item['name'] = $data[$key]['name'];
  return $item->save();
});

Answer 5

Its not possible because updation in bulk can only be possible via union which is the most slow operator you can use in sql. Otherwise there is no way to update in bulk with different values in different rows even in raw sql and therefore no way in laravel as well.

Answer 6

I wrote a batch update function to use in my Laravel projects. It may be useful for anyone who wants to use bulk update query in laravel. Its first parameter is the table name string, second is the key name string on which you wants to update the row or rows and mostly it will be the 'id', the third parameter is the data array in the format like:

array(
    array(
        'id' => 1,
        'col_1_name' => 'col_1_value',
        'col_2_name' => 'col_2_value',
        //....
    ),
    array(
        'id' => 2,
        'col_1_name' => 'col_1_value',
        'col_2_name' => 'col_2_value',
        //....
    ),
    //....
);

Function definition:

private function custom_batch_update(string $table_name = '', string $key = '', Array $update_arr = array()) {

    if(!$table_name || !$key || !$update_arr){
        return false;
    }

    $update_keys = array_keys($update_arr[0]);
    $update_keys_count = count($update_keys);

    for ($i = 0; $i < $update_keys_count; $i++) {
        $key_name = $update_keys[$i];
        if($key === $key_name){
            continue;
        }
        $when_{$key_name} = $key_name . ' = CASE';
    }

    $length = count($update_arr);
    $index = 0;
    $query_str = 'UPDATE ' . $table_name . ' SET ';
    $when_str = '';
    $where_str = ' WHERE ' . $key . ' IN(';

    while ($index < $length) {
        $when_str = " WHEN $key = '{$update_arr[$index][$key]}' THEN";
        $where_str .= "'{$update_arr[$index][$key]}',";
        for ($i = 0; $i < $update_keys_count; $i++) {
            $key_name = $update_keys[$i];
            if($key === $key_name){
                continue;
            }
            $when_{$key_name} .= $when_str . " '{$update_arr[$index][$key_name]}'";
        }
        $index++;
    }

    for ($i = 0; $i < $update_keys_count; $i++) {
        $key_name = $update_keys[$i];
        if($key === $key_name){
            continue;
        }
        $when_{$key_name} .= ' ELSE ' . $key_name . ' END, ';
        $query_str .= $when_{$key_name};
    }
    $query_str = rtrim($query_str, ', ');
    $where_str = rtrim($where_str, ',') . ')';
    $query_str .= $where_str;
    $affected = DB::update($query_str);

    return $affected;
}

It will produce and execute the query string like this:

UPDATE table_name SET col_1_name = CASE 
WHEN id = '1' THEN 'col_1_value' 
WHEN id = '2' THEN 'col_1_value' 
ELSE col_1_name END, 
col_2_name = CASE 
WHEN id = '1' THEN 'col_2_value' 
WHEN id = '2' THEN 'col_2_value' 
ELSE col_2_name END 
WHERE id IN('1','2')

Answer 7

It will work in laravel to update existing or new insertion into table.

$data=array(  
    array('id'=>1,'name'=>'kaleemullah@example.com'),
    array('id'=>2,'name'=>'kaleemullah@example.com2'),
    array('id'=>4,'name'=>'kaleemullah@example.com4')
);
$imported =  implode(', ', array_map(function ($string) {
return "(".$string['id'].','."'".$string['name']."'"."),";
}, $data));
$OneStepAway =  str_replace(',,', ',', $imported);
$kale = rtrim($OneStepAway,',');
$s = DB::statement("INSERT INTO  tests (id,name) VALUES $kale
ON DUPLICATE KEY UPDATE name=VALUES(name)") ;
echo "Successfully Updated and Insert New records into the database ";

Answer 8

For a small chunk of data, a loop would do fine.

However, if you have lots of data - definitely stay away from looping because "database transaction" is an expensive operation and with a for loop, you'll end up doing a transaction for each iteration.

Here you can see how to update without a for loop:

$data = [
  ['id'=>1,'name'=>'xxx'],
  ['id'=>2,'name'=>'xxx'],
  ['id'=>3,'name'=>'xxx'],
  ['id'=>4,'name'=>'xxx']
];

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

foreach ($data as $datum) {
   $id = $datum['id'];

   $cases[] = "WHEN {$id} then ?";
   $params[] = $datum['name'];
   $ids[] = $id;
}

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

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

If you want to read about it, check https://medium.com/@sentiasa/bulk-update-multiple-records-with-separate-data-laravel-3da9131c279a

Answer 9

public function transaction($callback) {
    DB::connection(**<TABLE_NAME>**)->transaction($callback);
}

public function updateBatch($batchData)
{
    $count = 0;
    try {
        $batchData = array_values($batchData);
        $total = count($batchData);
        $j = 0;
        $i = 0;
        do {
            $j = min($total -1, $j + self::$configBatch);
            DB::connection(**<TABLE_NAME>**)->transaction(function() use ($batchData, &$count, $total, $j, &$i) {
                for (; $i <= $j; $i++) {
                    $batchData[$i]->save();
                    $count++;
                }
            });
        } while ($i < $total);
    } catch (\Exception $exception) {
        print_r($exception->getMessage());
        $count = 0;
    }
    return $count;
}

Hope to help you