Level 50
@marymvlg28 Maybe try:
foreach ($data as $id => $status) {
MyModel::where('id', $id)->update('status_id', $status);
}
Jan 28, 2016
9
Level 2
Bulk update
Hi,
I'm wondering if there is a way to execute a bulk update like this:
$data = [ ["id" => 20, "status_id" => 1], ["id" => 21, "status_id" => 2], ["id" => 23, "status_id" => 3] ];
MyModel::update($data);
Where id = 20 set status_id = 1... and so on...
EDIT: If I had 1000 rows to update, I would have to do 1000 connections to the database, and that is just what I want to avoid, Codeigniter has a way to update multiple data in a single connection, which is much faster. I need to know if there is a way to do that in Laravel.
Level 2
But I'm looking for a way to do one update in batch.
Level 50
@marymvlg28 What are you talking about? Laravel will only create a single connection and just execute multiple queries.. It won't destroy and re-create the connection for each query...
Level 8
MySQL does not provide a way to do bulk updates in one query. But I think if you put multiple queries inside a transaction it will execute faster.
Level 2
Sorry @kfirba you're right. But still, executing one update would be faster that 1000.
Level 122
Yes, one update... it would just take 1000 times longer than 1 update, or actually, the same as you doing 1000 updates.
Level 2
Postgresql allows it. Codeigniter has or use to have a way, It concatenate every update statement with semicolons, and then submit the statements in one shot.
Level 2
I found the Codeigniter code and I did that way. It constructs an update query as a query raw where I can update several rows indicating the row id and the column-value to be updated.
Level 1
You can use "insert on duplicate key update....."
Please or to participate in this conversation.