Laravel Bulk UPDATE

Question

I am trying to update a table containing a slug value with random slugs for each record.

$vouchers = Voucher->get(); // assume 10K for example foreach ($vouchers as $voucher) { $q .= "UPDATE vouchers set slug = '" . Str::random(32) . "' WHERE id = " . $voucher->id . ";"; } DB::statement($q); 

There are about 2 million records, so I need to complete this as most. Doing it as separate entries is too long. I can’t find a way to mass launch them, for example, in groups of 10K or something like that.

I tried a bunch of variations ->update() and DB::statement , but it seems I couldn’t leave it.

Answer 1

I created a My Custom function for multiple updates, such as update_batch in CodeIgniter .

Just put this function in any of your models or you can create a helper class and put this function in this class:

 //test data /* $multipleData = array( array( 'title' => 'My title' , 'name' => 'My Name 2' , 'date' => 'My date 2' ), array( 'title' => 'Another title' , 'name' => 'Another Name 2' , 'date' => 'Another date 2' ) ) */ /* * ---------------------------------- * update batch * ---------------------------------- * * multiple update in one query * * tablename( required | string ) * multipleData ( required | array of array ) */ static function updateBatch($tableName = "", $multipleData = array()){ if( $tableName && !empty($multipleData) ) { // column or fields to update $updateColumn = array_keys($multipleData[0]); $referenceColumn = $updateColumn[0]; //eg id unset($updateColumn[0]); $whereIn = ""; $q = "UPDATE ".$tableName." SET "; foreach ( $updateColumn as $uColumn ) { $q .= $uColumn." = CASE "; foreach( $multipleData as $data ) { $q .= "WHEN ".$referenceColumn." = ".$data[$referenceColumn]." THEN '".$data[$uColumn]."' "; } $q .= "ELSE ".$uColumn." END, "; } foreach( $multipleData as $data ) { $whereIn .= "'".$data[$referenceColumn]."', "; } $q = rtrim($q, ", ")." WHERE ".$referenceColumn." IN (". rtrim($whereIn, ', ').")"; // Update return DB::update(DB::raw($q)); } else { return false; } } 

He will produce:

 UPDATE `mytable` SET `name` = CASE WHEN `title` = 'My title' THEN 'My Name 2' WHEN `title` = 'Another title' THEN 'Another Name 2' ELSE `name` END, `date` = CASE WHEN `title` = 'My title' THEN 'My date 2' WHEN `title` = 'Another title' THEN 'Another date 2' ELSE `date` END WHERE `title` IN ('My title','Another title') 

Answer 2

If someone lands on this page like me, laravel allows bulk updates like:

$affectedRows = Voucher::where('id', '=', $voucher->id)->update(array('slug' => Str::random(32)));

See "Updating the resulting model" in the section http://laravel.com/docs/4.2/eloquent#insert-update-delete

Answer 3

Quantization results are the best way to do such things without eating all your RAM, and Laravel support results out of the box.

For instance:

  Voucher :: chunk (2000, function ($ vouchers)
   {
     foreach ($ vouchers as $ voucher)
     {
         //
     }
  });

Answer 4

I made a bulk update function for use in my Laravel projects. This is much more efficient than most of the features I found on the Internet. This may be useful for those who want to use a batch update request in Laravel. Its first parameter is the table name row, the second is the key name row, based on which you want to update the row or rows, and most of the time it will be "id", and the third parameter is an array of data in the following format

 array( array( 'id' => 1, 'col_1_name' => 'col_1_val', 'col_2_name' => 'col_2_val', //... ), array( 'id' => 2, 'col_1_name' => 'col_1_val', 'col_2_name' => 'col_2_val', //... ), //... ); 

The function will return the number of rows affected. 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 create and execute the query string as follows:

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