PHP
Efficiently Updating Multiple Records with a Single Query
Learn how to perform efficient batch updates on multiple Eloquent records using a single database query, significantly improving performance for bulk data modifications.
// Assume a 'products' table with 'category_id', 'status', 'price'
// Scenario 1: Update status for all products in a specific category
App\Models\Product::where('category_id', 5)
->update(['status' => 'inactive']);
// Scenario 2: Increment/Decrement a numeric column for matching records
// Increase price by 10 for all products in category 5
App\Models\Product::where('category_id', 5)
->increment('price', 10);
// To increment by a percentage (e.g., 10% of current price):
App\Models\Product::where('category_id', 5)
->update(['price' => \DB::raw('price * 1.10')]);
// Scenario 3: Update multiple columns for specific IDs
$productIds = [10, 15, 22];
App\Models\Product::whereIn('id', $productIds)
->update([
'status' => 'discounted',
'updated_at' => now(), // Manually update timestamp
]);
// Note: `updated_at` and `created_at` timestamps are NOT automatically managed
// by Eloquent when using mass update directly on the query builder.
// You must manually set `updated_at` if desired.
How it works: Eloquent allows you to update multiple records efficiently with a single database query using the `update()` method on a query builder instance. Instead of fetching each model and saving it individually, you define your conditions (`where`, `whereIn`, etc.) and then pass an associative array of columns and their new values to `update()`. This approach is significantly faster for large sets of records, as it bypasses model events and saves on individual database calls. For incrementing/decrementing numeric columns, `increment()` and `decrement()` methods are available, or you can use `DB::raw()` for more complex updates. Remember that timestamps are not automatically handled with mass updates, so `updated_at` needs to be set manually if required.