Laravel Update Query with multiple columns, Conditions

Sikander Sharma 0
Viewed 39

Can we update multiple columns based on multiple different condition in Laravel using Eloquent Lets Say, I want to

Design::where('name','A')->update(['override_value'=>'some_value1']);

Design::where('name','B')->update(['override_value'=>'some_value2']);

Design::where('name','C')->update(['override_value'=>'some_value3']);

Do i have to repeat this query or is there a way to do this in a single update query ?

eloquent laravel
4 Answers

Use query builder whereIn clause

e.g.

DB::table('designs')->whereIn('name', ['A', 'B', 'C'])->update(['override_value'=>'some_value']);

another way if you need different values is by using upsert

e.g.

Design::upsert([
    ['name' => 'A', 'override_value' => 'some_value1'],
    ['name' => 'B', 'override_value' => 'some_value2'],
    ['name' => 'C', 'override_value' => 'some_value3'],
], ['name'], ['override_value']);

just make sure the name already exist in your tables, otherwise it will create new record

userRjx50

Sure you can, using whereIn method and passing an array with multiple values in update method as first parameter.

 Design::whereIn('name', ['A', 'B'])->update(['override_value'=>'some_value', 'some_other_value' => 'new value']);
Gazmend Sahiti0

If you would like to do that in one line, your data must be an associative array like this

$data = [
    "A" => "some_value1",
    "B" => "some_value2",
    "C" => "some_value3",
];

and then loop through it and update the records like this

foreach ($data as $key => $value) {
    Design::where('name', $key)->update(['override_value' => $value]);
}
Myo Win0

I think you are looking for upsert(), I've used them alot to update thousands of records, all with their own changes. The only requirement to use it is that you can only specify the identifiers with actual unique keys. Check here for more info.

//An array to store the changes in.
$updates = [];
//You can fill the $updates array by looping over db-data that you want to manipulate 
//For simplicity sake i'm gonna fill them manually
$updates[] = [
  'name' => 'A',//identifier. Please note you can also use id if you have no other unique key
  'override_value' => 'X'//insert what you want to change for this identifier
];
$updates[] = [
  'name' => 'B',
  'override_value' => 'Y'
];
$updates[] = [
  'name' => 'C',
  'override_value' => 'Z'
];
//Upsert 
//First parameter expects the array with identifiers and updates
//Second parameter is list of keys to identify the record.
//Third parameter is list of keys to update
Design::upsert($updates, ['name'], ['override_value']);

Just beware that using upsert does not throw events on the model! Some libraries will require models to throw events(like laravel-auditing)

Rob Biermann0
Related