In MySQL 8.0, you can get help on two window functions:

• MAX, retrieve the maximum "group" value
• ROW_NUMBER, retrieves the incremental value for each NULL present in the table.

You can then sum these two values and update the table with the "Group" field being empty.

WITH cte AS ( SELECT id, name, MAX(group_) OVER() + ROW_NUMBER() OVER(PARTITION BY group_ IS NULL ORDER BY name) AS new_group FROM tab ) UPDATE tab INNER JOIN cte ON tab.id = cte.id AND tab.name = cte.name SET tab.group_ = cte.new_group WHERE tab.group_ IS NULL;

See the demohere.

---

In MySQL 5.X, you can use a variable, initialize it with the largest "group" value, and then update it incrementally in aUPDATEstatement. >SET clause.

SET @maxgroup = NULL; SELECT MAX(group_) INTO @maxgroup FROM tab; UPDATE tab SET group_ = (@maxgroup:= @maxgroup + 1) WHERE group_ IS NULL; ORDER BY id;

See the demohere.