CODEWITHSUNDEEP
phplaravellaravel-5
Abhay Maurya
Abhay Maurya

Reputation: 12277

Update in bulk - Laravel

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?

Upvotes: 0

Views: 11177

Answers (9)

Kieu Trung
Kieu Trung

Reputation: 169

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

Upvotes: 0

senty
senty

Reputation: 12847

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

Upvotes: 0

Kaleemullah
Kaleemullah

Reputation: 544

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

$data=array(  
    array('id'=>1,'name'=>'[email protected]'),
    array('id'=>2,'name'=>'[email protected]'),
    array('id'=>4,'name'=>'[email protected]')
);
$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 ";

Upvotes: 0

Khalil Kamran
Khalil Kamran

Reputation: 65

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')

Upvotes: 0

user7852277
user7852277

Reputation:

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.

Upvotes: 0

Yat23
Yat23

Reputation: 171

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();
});

Upvotes: 0

z1haze
z1haze

Reputation: 440

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

Upvotes: 0

Marcin Gierus
Marcin Gierus

Reputation: 26

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.

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163898

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

Upvotes: 0

Related Questions