Update Table without loops

Question

There is a table foo and it has a column called fooPos. I ned to update the fooPos column with respect to id.

I have the following data

$id = [21,23,34,56,76];
$fooPos = [1,2,3,4,5];

How can I update this without using loops? It's like 21(id) => 1(fooPos), 23 => 2, 34 =>3 etc.,

It is wrong, we shouldn't be doing any update inside a loop. What if we get 1,00,000 rows to be updated? Is it still okay to update in a loop? — Rehan, Aug 27 '19 at 07:13
do you mean put all date in the table without create loops like this : fooPos=myData, and generate table automatically !! did you mean this . — Abd Abughazaleh, Aug 27 '19 at 07:28
You can't. Update queries can only update rows with the same filter and updates at a time. So you will have to write an update query for each of these ids unless there are rows that will get the same `$fooPos`. — Jerodev, Aug 27 '19 at 08:06
@Jerodev there are many solution to update multiple rows at the same time (with one query). check my answer and https://stackoverflow.com/a/3466/4369919 — N69S, Aug 27 '19 at 08:13

score 0 · Answer 1 · answered Aug 27 '19 at 08:09

You have a solution with INSERT INTO ... ON DUPLICATE KEY UPDATE..., more details in here Multiple update

That solution can trigger an error if the ID doesn't exist and you have some other required fields. In that cas you ca use this solution:

$updateSets = [];
$ids = [21,23,34,56,76];
$fooPos = [1,2,3,4,5];
foreach ($ids as $key => $id) {
    $updateSets[] = 'SELECT '.$id.' as set_id, '.$fooPos[$key].' as pos ';
}

$updateSetsString = implode(' UNION ALL ', $updateSets);

\DB::statement('UPDATE your_table JOIN ('.$updateSetsString.') up_set ON your_table.id = up_set.set_id SET your_table.pos = up_set.pos');

score 0 · Answer 2 · answered Aug 27 '19 at 08:25

function updateTableWithoutQueryLoops()
{
    try {

        $id = collect([21,23,34,56,76]);
        $fooPos = collect([1,2,3,4,5]);
        // To check both parameters should have an equal number of elements.
        if(count($id) == count($fooPos) ) {
            $combinedValues = $id->combine($fooPos);
        } else {
            return 'Please check equal number of elements for give arrays.';
        }

        // Run foreach loop of Combined values
        foreach ($combinedValues as $id => $fooPos) {
            $id = (int) $id;
            $cases[] = "WHEN {$id} then ?";
            $params[] = $fooPos;
            $ids[] = $id;
        }

        $ids = implode(',', $ids);
        $cases = implode(' ', $cases);
        $params[] = \Carbon\Carbon::now();

        return \DB::update("UPDATE `foo` SET `fooPos` = CASE `id` {$cases} END, `updated_at` = ? WHERE `id` in ({$ids})", $params);

    } catch (\Exception $e) {
        return 'Exception message:' . $e->getMessage() . ' with code: ' . $e->getCode();
    }
}

function updateTableWithoutQueryLoops()
{
    try {

        $id = collect([21,23,34,56,76]);
        $fooPos = collect([1,2,3,4,5]);
        // To check both parameters should have an equal number of elements.
        if(count($id) == count($fooPos) ) {
            $combinedValues = $id->combine($fooPos);
        } else {
            return 'Please check equal number of elements for give arrays.';
        }

        // Run foreach loop of Combined values
        foreach ($combinedValues as $id => $fooPos) {
            $id = (int) $id;
            $cases[] = "WHEN {$id} then ?";
            $params[] = $fooPos;
            $ids[] = $id;
        }

        $ids = implode(',', $ids);
        $cases = implode(' ', $cases);
        $params[] = \Carbon\Carbon::now();

        return \DB::update("UPDATE `foo` SET `fooPos` = CASE `id` {$cases} END, `updated_at` = ? WHERE `id` in ({$ids})", $params);

    } catch (\Exception $e) {
        return 'Exception message:' . $e->getMessage() . ' with code: ' . $e->getCode();
    }
}

Update Table without loops

2 Answers2