CODEWITHSUNDEEP
phplaravelbulk
Bobby
Bobby

Reputation: 375

Laravel 5.0 bulk update

I would like to execute multiple update statements rather than executing them individually in order to avoid multiple DB hits.Following is the code

$updateStatement = '';
    foreach ($users as $user) {
        $i++;
        $updateStatement = $updateStatement. "update users set packet_seqno = '".$i."' where id = ".$user->id . ';  ';      
    }
    \DB::statement($updateStatement);

However, it is not working and throwing error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update

Can you please help me to fix this?

Here is the dump of the $updateStatement.

"update ppt_import_mortgage1 set packet_seqno = '1' where id = 37;  update ppt_import_mortgage1 set packet_seqno = '2' where id = 39;  update ppt_import_mortgage1 set packet_seqno = '3' where id = 40;  update ppt_import_mortgage1 set packet_seqno = '4' where id = 42;  update ppt_import_mortgage1 set packet_seqno = '5' where id = 43;  update ppt_import_mortgage1 set packet_seqno = '6' where id = 44;  update ppt_import_mortgage1 set packet_seqno = '7' where id = 45;  update ppt_import_mortgage1 set packet_seqno = '8' where id = 46;  update ppt_import_mortgage1 set packet_seqno = '9' where id = 47;  update ppt_import_mortgage1 set packet_seqno = '10' where id = 48;  "

Upvotes: 0

Views: 1342

Answers (2)

Emeka Mbah
Emeka Mbah

Reputation: 17520

If you wish to use eloquent, you could simply do this:

foreach ($users as $user) {
       $i++;
    User::where('id',$user->id)->update(['packet_seqno' => $i]);
}

or better:

foreach ($users as $user) {
       $i++;
    $user->update(['packet_seqno' => $i]);
}

Upvotes: 1

Gaurav Dave
Gaurav Dave

Reputation: 7474

Try this:

$updateStatement = '';
   foreach ($users as $user) {
       $i++;
       $updateStatement = "update users set packet_seqno = '".$i."' where id = ".$user->id; 
    \DB::update(\DB::raw($updateStatement));     
}

See, if that helps.

Upvotes: 0

Related Questions