Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

rahul95's avatar
Level 15

Is there anyway to perform update / increment values of multiple rows with different values in laravel in a single query

Let's say i have a collection of user model,

$users = [
    [
        'id' => 1,
        'email' => '[email protected]',
        'total_reward_received' => 1000
    ],
    [
        'id' => 2,
        'email' => '[email protected]',
        'total_reward_received' => 1500
    ]
    [
        'id' => 3,
        'email' => '[email protected]',
        'total_reward_received' => 2500
    ]
]

Here, I want to update the value of total_reward_recieved by incrementing it's value with the new reward the user got. Let's say I have an array of new reward values,

[
    [
        'user_id' => 1,
        'new_reward_amount' => 500
    ]
    [
        'user_id' => 3,
        'new_reward_amount' => 200
    ]
]

I want the total_reward_received value of user one to be updated to 1500 and that of user three to be 2700. Like this,

[
    [
        'id' => 1,
        'email' => '[email protected]',
        'total_reward_received' => 1500
    ],
    [
        'id' => 3,
        'email' => '[email protected]',
        'total_reward_received' => 2700
    ]
]

Currently this is how I am doing it,

foreach ($users as $key => $user) {
    $new_reward = calculateNewRewardAmount();
    User::where('id', $user->id)->increment('total_reward_received', $new_reward);
}

Since I will have thousands of users to deal with, I think this way of creating a User instance within the loop is not the right way to go about. If I can prepare an array with the user_id and new_reward_amount within the loop, is there anyway I can perform the increment option with a single query.

What I have tried

I have tried using https://github.com/mavinoo/laravelBatch library for a batch update. But since I want the column to be incremented instead of updating with a new value I can't see a way to do it. I have used DB::raw() to increment the value, but it doesn't seems to be working, If I try with a static value (a variable) instead, it seems to be working. Have a look,

$newRewardsArray = [];
foreach ($users as $key => $user) {
    $newRewardsArray[$key] = [
        'id' => $user->id,
        'total_reward_received' => $newReward + $existingReward
    ];
}

Batch::update(new User, $newRewardsArray, 'id');

This one seems to be working. where i have to call the user model to get the existing reward.

$newRewardsArray = [];
foreach ($users as $key => $user) {
    $newRewardsArray[$key] = [
        'id' => $user->id,
        'total_reward_received' => DB::raw('total_reward_received+' . $newReward)
    ];
}

Batch::update(new User, $newRewardsArray, 'id');

But this one not. (But would've been great ;-)) Here I am trying to increment the value by new_reward_amount using DB::raw(). But the value of the column is getting updated as '0'.

Do you guys have any idea on how to achieve this. Even without using this library. (I just included that code example from the library to explain how I tried it.)

0 likes
6 replies
automica's avatar

@rahul95 you could make it more efficient using chunk


$updates = [
    [
        'user_id' => 1,
        'new_reward_amount' => 500
    ]
    [
        'user_id' => 3,
        'new_reward_amount' => 200
    ]
];


 User::chunk(100, function($updates)
  {
    foreach ($updates as $update)
    {
    User::where('id', $update['user_id'])->increment('total_reward_received', $update['new_reward_amount']);
    }
 });
rahul95's avatar
Level 15

But still, if there is 1000 users, the User Model will be called 1000 times right ? I think it uses more server resource right ? I'm wondering if I can encapsulate all of it in a single query.

automica's avatar

@rahul95 as you are incrementing, you'll still need to load this data from the user table before updating.

The alternative is to do a query to get all users you have to update, and return this as collection of users.

At that point you'll know what the value is you are incrementing. You could then add the 'increment' by iterating through the collection.

You could then prepare this an a SQL update statement then execute as a single query at the end.

For me though, this adds more complexity and time. Considering that the number of users you are updating isn't massive. With chunking, you are using less resources than you would be if you were foreaching on a 1000's users.

Best way to test performance is to spin up a test to do it with faked data and just measure.

rahul95's avatar
Level 15

as you are incrementing, you'll still need to load this data from the user table before updating.

No when using the eloquent increment function, i think it will take care of the existing value, since we are passing the column name and then the value to be incremented.

With chunking, you are using less resources than you would be if you were foreaching on a 1000's users.

Yeah, I think that would be great. I am kind of wondering if there is any more efficient way to achieve this. With a single query.

Till i find one out, i think chunking would be the way to go.

automica's avatar
automica
Best Answer
Level 54

@rahul95 If you are doing thousands and thousands of rows of updates then a less hungry way may be beneficial.

For now though the most efficient way is the one that takes you less time to code IMHO.

Please or to participate in this conversation.