Bulk updating in Laravel with specific where condition for each row

Question

I am developing a database-driven website using Laravel. Now I am having a problem with updating database. I want to run bulk update. Normally I run like this.

 $data = [
       [
         'col_1'=>$value1
       ],
       [
         'col_1'=>$value2
       ],
       [
         'col_1'=>$value3
       ],
       [
         'col_1'=>$value4
       ]
    ];
    MyTableObject::update($data)->where('col_2'=>$col_2_val);

As you can see in the above code, where clause is checking for only one condition for all rows that are to be updated. But what I want is that I want the different where clause condition for each row or query. To use foreach and run query for each row, it will be time-consuming because I have to update a lot of rows. To demonstrate it, it will be something like this.

$data = [
       [
         'col_1'=>$value1,
         'col_2'=>$where_value_1 // This is not the column to be updated. That is for where clause.
       ],
       [
         'col_1'=>$value2,
         'col_2'=>$where_value_2 // This is not the column to be updated. That is for where clause.
       ],
       [
         'col_1'=>$value3,
         'col_2'=>$where_value_3
       ],
       [
         'col_1'=>$value4,
         'col_2'=>$where_value_4
       ]
    ];
    MyTableObject::update($data)->where('where_col_name'=>'col_2');

I found this link, but the answers are not clear and complete. Is it possible to do it in Laravel, and how can I do it?

Why not just running a foreach on the array? It shouldn't be time consuming... — ka_lin, Oct 26 '17 at 14:18
I have to update thousands of records. So I have to run thousands of query for each row. It is time-consuming. Yes, I have to use foreach for sure but what I want to know is how can I update in one query? — Wai Yan Hein, Oct 26 '17 at 14:23
Try MyTableObject::where('where_col_name'=>'col_2')->update($data); — Tuim, Oct 26 '17 at 14:46
Your query is basically a set of multiple where clause to change a field for each where condition. So, I don't think it can be done in a single query. You definitely need to consider using a for loop, or maybe your database model is not quite right. — sha-1, Oct 26 '17 at 15:12
Then why not running it as a cron or when an update is needed push it in a queue and treat it — ka_lin, Oct 27 '17 at 04:51

Khalil Kamran · Answer 1 · 2018-02-14T05:57:15.190

Here is the batch update function which I wrote to use in my Laravel projects. Its first parameter is the table name string, second is the key name string on which you want to update the row or rows and mostly it will be the 'id', the third parameter is the data array in the format:

array(
    array(
        'id' => 1,
        'col_1_name' => 'col_1_val',
        'col_2_name' => 'col_2_val',
        //.....
    ),
    array(
        'id' => 2,
        'col_1_name' => 'col_1_val',
        'col_2_name' => 'col_2_val',
        //.....
    ),
    //.....
);

Function:

private function custom_batch_update(string $table_name = '', string $key = '', Array $update_arr = array()) {

    if(!$table_name || !$key || !$update_arr){
        return false;
    }

    $update_keys = array_keys($update_arr[0]);
    $update_keys_count = count($update_keys);

    for ($i = 0; $i < $update_keys_count; $i++) {
        $key_name = $update_keys[$i];
        if($key === $key_name){
            continue;
        }
        $when_{$key_name} = $key_name . ' = CASE';
    }

    $length = count($update_arr);
    $index = 0;
    $query_str = 'UPDATE ' . $table_name . ' SET ';
    $when_str = '';
    $where_str = ' WHERE ' . $key . ' IN(';

    while ($index < $length) {
        $when_str = " WHEN $key = '{$update_arr[$index][$key]}' THEN";
        $where_str .= "'{$update_arr[$index][$key]}',";
        for ($i = 0; $i < $update_keys_count; $i++) {
            $key_name = $update_keys[$i];
            if($key === $key_name){
                continue;
            }
            $when_{$key_name} .= $when_str . " '{$update_arr[$index][$key_name]}'";
        }
        $index++;
    }

    for ($i = 0; $i < $update_keys_count; $i++) {
        $key_name = $update_keys[$i];
        if($key === $key_name){
            continue;
        }
        $when_{$key_name} .= ' ELSE ' . $key_name . ' END, ';
        $query_str .= $when_{$key_name};
    }
    $query_str = rtrim($query_str, ', ');
    $where_str = rtrim($where_str, ',') . ')';
    $query_str .= $where_str;
    $affected = DB::update($query_str);

    return $affected;
}

It will produce and execute the query string like this:

UPDATE table_name SET col_1_name = CASE 
WHEN id = '1' THEN 'col_1_value' 
WHEN id = '2' THEN 'col_1_value' 
ELSE col_1_name END, 
col_2_name = CASE 
WHEN id = '1' THEN 'col_2_value' 
WHEN id = '2' THEN 'col_2_value' 
ELSE col_2_name END 
WHERE id IN('1','2')

score 0 · Answer 2 · answered Feb 02 '18 at 12:14

If I'm understanding this correctly, col_1 is the value you wish to use in update and col_2 is the value you're querying against. Try the following (may need some adjustments)

collect($data)->each(function(array $row) {
   MyTableObject::where('where_col_name', $row['col_2'])->update(['col_1' => $row['col_1']]);
});

Bulk updating in Laravel with specific where condition for each row

2 Answers2