Laravel: Sequentially Incrementing Null Columns in a Database

SequentialIncrementNullColumns.php

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class SequentialIncrementNullColumns extends Seeder
{
    public function run(): void
    {
        $maxPosition = '(SELECT IFNULL(MAX(`position`), 0) FROM `sections`)';

        DB::transaction(function () use ($maxPosition) {
            DB::statement("SET @pos := $maxPosition");
            DB::update("UPDATE `sections` SET `position` = (@pos := @pos + 1) WHERE `order` IS NULL AND `position` IS NULL ORDER BY `id`");
        });
    }
}

Another way.

SQL Query

UPDATE `sections`
INNER JOIN (
    SELECT `id`, (@row_number:=@row_number + 1) AS new_position
    FROM `sections`, (SELECT @row_number := IFNULL((SELECT MAX(`position`) FROM `sections`), 0)) AS init
    WHERE `order` IS NULL AND `position` IS NULL
) AS subq ON `sections`.`id` = subq.`id`
SET `sections`.`position` = subq.new_position;

~ OR ~

Eloquent

Section::joinSub('
    SELECT `id`, (@row_number:=@row_number + 1) AS new_position
    FROM `sections`, (SELECT @row_number := IFNULL((SELECT MAX(`position`) FROM `sections`), 0)) AS init
    WHERE `order` IS NULL AND `position` IS NULL
', 'subq',
  'sections.id', '=', 'subq.id')->update(['position' => DB::raw('`subq`.`new_position`')]);