6

I was learning about How to insert multiple rows from a single query using eloquent/fluent and I found the answer here

Can somebody share any documentation about how to update bulk rows in single query?

My queries are below.

Update tblrole set role = 'Super Admin' where RoleID = 1;
Update tblrole set role = 'Super Admin A' where RoleID = 2;
Update tblrole set role = 'Super Admin B' where RoleID = 3;
Update tblrole set role = 'Super Admin C' where RoleID = 4;
Pankaj
  • 9,749
  • 32
  • 139
  • 283

3 Answers3

1

You can solve the issue using a single MySQL query. It can be implemented in Laravel Eloquent using DB::raw() method.

**UPDATE** tblrole **SET** role =
    **CASE** 
      WHEN RoleID = 1 THEN 'Super Admin'
      WHEN RoleID = 2 THEN 'Super Admin A'
      WHEN RoleID = 3 THEN 'Super Admin B'
      WHEN RoleID = 4 THEN 'Super Admin C'
    **END**
 **WHERE** RoleID in (1,2,3,4);
Ishrat Sharmin
  • 111
  • 1
  • 4
  • the id i mentioned in the question were sample. I am looking for a generic solution like insert has. – Pankaj Jul 03 '17 at 16:08
0

You cannot do anything like this in simple way. You can easily update multiple rows with same value but if you want to update role column with different values it will be tricky.

In fact it doesn't make much sense to do it like this but if you really want it and you think it's the best solution you can try to play with raw queries using technique described here https://stackoverflow.com/a/25674827/3593996

Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
  • Did u check the bulk insert link in my question? If that makes sense, then why can't update? – Pankaj Jun 25 '17 at 08:46
  • Reason for this is, I don't want to open/ close db connection for each update statement. These statements will be executed in a loop. – Pankaj Jun 25 '17 at 08:51
  • Why would you think you "open / close" the db connection for each update, you can run all these queries on the same instance –  Jun 25 '17 at 10:04
  • @Pankaj Normally you use same DB connection until end of script so if you run for example 100 update queries all we be using same DB connection – Marcin Nabiałek Jun 25 '17 at 18:26
  • any idea about who to run all update statements batch in one shot like insert? – Pankaj Jun 25 '17 at 18:27
  • @Pankaj: Have you checked the logic behind insert? I think it will loop over the array and insert it one be one. If you want to fire one string to the database, you can convert all your laravel updates to sql `->toSql()`, implode them with an `;` and fire an sql raw to the database. I don't recommend this way because it's very hard to debug. – cre8 Jul 01 '17 at 09:36
0

You might find inspiration from this mass insert or update gist:

/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
* 
* insertOrUpdate([
*   ['id'=>1,'value'=>10],
*   ['id'=>2,'value'=>60]
* ]);
* 
*
* @param array $rows
*/

function insertOrUpdate(array $rows){
    $table = \DB::getTablePrefix().with(new self)->getTable();


    $first = reset($rows);

    $columns = implode( ',',
        array_map( function( $value ) { return "$value"; } , array_keys($first) )
    );

    $values = implode( ',', array_map( function( $row ) {
            return '('.implode( ',',
                array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
            ).')';
        } , $rows )
    );

    $updates = implode( ',',
        array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
    );

    $sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";

    return \DB::statement( $sql );

}

Ref: https://gist.github.com/RuGa/5354e44883c7651fd15c

I don't think I need to provide any explanation as each chunk of code in the function speaks of itself.

Oluwatobi Samuel Omisakin
  • 6,944
  • 3
  • 33
  • 48