Laravel 5 update multiple rows with different values

Ask Time：2016-07-14T16:59:31 Author：George Dimitriadis

I have been searching everywhere for a solution to this, I hope someone has already solved it or has some good ideas for improvement.

I am using Laravel 5 and I have come accross a situation where I need to update many rows with new values. Right now I'm doing a for-loop for all those rows to update them and I would like to optimize this so that I don't run many sql queries. Here's an example code:

<?php

   //Well, the original code looks better than this, but the concept is the same

   $myrows = [0,1,2,3,4,5,6,7,8,9];
   $myvalues = [45,543,657,574,234,26457,2462,897,234,89032];

   for($i=0;$i<count($myrows);$i++) {
      MyClass::where('id', $myrows[$i])->update(['myColumn' => $myvalues[$i]]);
   }

?>

Obviously this will execute 10 queries (the same amount as the rows I want to update), but I want to do this with only one query, for optimization purposes. I am aware of the ability to update many rows at the same time with whereIn(...)->update(...) but with this method you can only update all rows to the same value, not different ones like in my example.

Thanks in advance for any help !

Author:George Dimitriadis，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/38369830/laravel-5-update-multiple-rows-with-different-values

NITHIN RAJ T :

$myRows = [0,1,2,3,4,5,6,7,8,9];\n$myValues = [45,543,657,574,234,26457,2462,897,234,89032];\n\nif (empty($myRows)) {\n // return or throw exception\n return;\n}\nif (count($myRows) != count($myValues) {\n // in case of dynamic values for $myRows/$myValues\n return;\n}\n$cases = [];\n$cases[] = "CASE id";\n\nfor ($myRows as $row) {\n $cases[] = "WHEN {$row} THEN ?"; \n}\n\n$cases = implode(" ", $cases);\n$ids = implode(",", $myRows);\n\nDB::update("UPDATE `my_table` SET `my_column` = {$cases} END WHERE `id` in ({$ids})", $myValues);\n",

2021-06-02T14:43:46

Abilogos :

in SQL every single query is executing in sequence.\nforeach ( array_combine($myrows,$myvalues) a $id => $val){\n MyClass::find($val)->update(['myColumn' => $val]);\n}\n\nonly thing you can do is to append queries, and send it once,\nsomething like this:\n$builder = DB::table((new MyClass)->getTable());\n$sql='';\n\nforeach ( array_combine($myrows,$myvalues) a $id => $val){\n $sql .= $builder->getGrammar()->insert($builder->where('id', $id),['myColumn' => $val]);\n}\nDB::connection()->getPdo()->prepare($sql)->execute();\n",

2021-06-02T15:15:43

Laravel 5 update multiple rows with different values