Question 74548 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel bulk update with bindings

I want to make a bulk update to my users table. I am not updating multiple rows to the same field value – each field value could be different as it will be supplied from user input.

I want to batch my updates and use a single sql connection to send multiple update statements.

Laravel unprepared works fine but it doesn’t bind parameters:

DB::unprepared("
UPDATE users set name = 'test1' where id = 43;
UPDATE users set name = 'test2' where id = 54;
");

Where test1 & test2 are variables which have been supplied via user input and therefore need to be bound before executing this sql statement.

I don’t want to sanitise these manually. Is there a way I can bind these parameters through a php/laravel function, but also use this type of bulk update?

Tags:

2

Answers


  1. 0

    As explained in Bulk Update Multiple Records with Separate Data by Bertug Korucu

    If you want to bulk update with one query, you can use this format

    UPDATE users
SET name = CASE  
              WHEN id = 43 THEN 'test1'
              WHEN id = 54 THEN 'test2'
           END 
WHERE ID IN (43, 54)

    Which with eloquent would look like this

    $cases= [];
$ids = [];
$params = [];

foreach ($users as $user) {
    $cases[] = "WHEN {$user->id} then ?";
    $params[] = $user->name;
    $ids[] = $user->id;
}

$ids = implode(',', $ids);
$cases = implode(' ', $cases);

if (!empty($ids)) {
    DB::update("UPDATE users SET `name` = CASE `id` {$cases} END WHERE `id` in ({$ids})", $params);
}

    If you have a lot of entries, would be a good idea to chunk by 500 or 1000 (depends on your DB configuration of placeholder limit)

    Login or Signup to reply.
  2. 0

    You can use the the following references :

    1. https://github.com/laravel/ideas/issues/575

    This one is a package. I can use this for updating few rows from millions of rows

    1. https://github.com/mavinoo/laravelBatch
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search