How to update multiple rows from a single query us

Question

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;

Answer 1

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.

Answer 2

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

Answer 3

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);