Update multiple rows of database in Laravel

Update multiple rows of database in Laravel

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Batch Updating JSON Data in Laravel: Beyond Raw SQL Loops

Executing multiple UPDATE statements for different rows is a common pattern when dealing with bulk data modifications, but the approach you need depends heavily on how your application scales. The raw SQL solution presented in the source material demonstrates a clear understanding of database mechanics: fetching records, iterating through them, modifying the payload, and executing batch updates. While this works for small datasets, it introduces significant bottlenecks as volume increases. Specifically, constructing a massive string with dozens or hundreds of concatenated UPDATE queries forces the database engine to parse and execute multiple individual statements within a single transaction context, which can lead to high network latency and connection overhead.

The provided Laravel snippet attempts to solve this by building a dynamic SQL string using DB::unprepared(). While this consolidates the execution into one go, it reintroduces complexity by bypassing the query builder's safety features. More critically, decoding JSON strings in PHP (json_decode) solely to re-encode them (json_encode) inside a loop is computationally expensive. If you are managing thousands of rows, the CPU time spent on this serialization overhead often outweighs the cost of the database write itself.

Laravel offers a more idiomatic and performant way to handle this scenario using Eloquent's batch update capabilities or direct query optimizations. The core issue with the example code is that it iterates over every row in PHP memory just to run an SQL statement. A better strategy involves utilizing DB::table()->update() with a raw expression or leveraging Laravel's built-in batching if you are strictly modifying data based on conditions rather than complex object transformations.

If your goal is to update the data column for multiple rows where project_id matches, but the specific JSON content varies per row, you must acknowledge that Eloquent cannot natively handle "apply this logic to every matching row" without iteration. However, you can significantly optimize the loop by avoiding redundant database hits and minimizing string manipulation.

Consider a scenario where you have a list of fields and need to update the default key within the JSON stored in the data column. Instead of fetching all rows into an array, decoding, modifying, and then looping back to execute raw SQL, you can leverage DB::table() combined with a single UPDATE statement that uses conditional logic if the schema allows, or process the data in chunks.

Here is a more efficient pattern using Laravel's query builder to construct a dynamic update statement without falling into the trap of unprepared statements:

$valueTable = DB::table('values');

// Fetch only the IDs and current data needed for modification
$records = $valueTable->where('project_id', $id)->get(['id', 'data']);

$sqlParts = [];

foreach ($records as $record) {
    $currentData = json_decode($record->data, true);
    
    // Only proceed if a default value was provided for this field
    if (!empty($field->default)) {
        $currentData['default'] = $field->default;
        $newData = json_encode($currentData);

        // Construct individual update clauses
        $sqlParts[] = "UPDATE values SET data = '$newData' WHERE id = {$record->id}";
    }
}

if (!empty($sqlParts)) {
    // Execute all updates in a single batch
    DB::connection()->getPdo()->exec(implode(";", $sqlParts));
}

While this still uses raw execution, it moves the loop logic to the PDO level where drivers like MySQL handle batched prepared statements more efficiently than executing multiple separate queries. However, the cleanest and most maintainable approach in modern Laravel often involves using DB::table()->update() with a single query that applies logic per row if the transformation is simple.

If the JSON structure is complex and requires PHP-side manipulation for every row, sticking to Eloquent's each or iterating via get() with explicit updates is acceptable for moderate data sizes. The key trade-off here is between development speed (using raw SQL) and maintainability (using Eloquent). For large-scale operations, consider whether the application architecture should be changed to store scalar values instead of JSON blobs if frequent bulk updates are required. This allows you to use standard Eloquent batch updates without manual string concatenation or unprepared blocks.

For further insights on database optimization patterns in Laravel, including handling complex data types and scaling strategies, visit https://laravelcompany.com. They often discuss advanced scenarios where ORM limitations meet high-performance requirements, providing context on when to stick with raw SQL versus leveraging the framework's abstractions.

Our network