I have a (MYSQL) table in the following format; assume the name of the table is mytable:

id is unique and auto-increments. name is a unique string, group is just an integer

I now want to assign name4 to a new group that does not exist yet, so the group for name4cannot be 1 or 2 in this example.

The result could,for example, be:

At the moment I am sorting by group descending and just insert the highest number 1 manually, but I was wondering if there was a better/quicker way to generate a new, unique value in a column. group has no other constraints, besides being an integer.

I am using the MySQL Workbench, so I can work with both SQL commands, as well as Workbench-specific options, if there are any.

If anything is unclear I'll gladly provide clarification.

CodePudding user response：

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

• MAX, to retrieve the maximum "group" value
• ROW_NUMBER, to retrieve the incremental value for each NULL existing in your table.

You can then sum up these two values and update your table where your "group" field is null.

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;

Check the demo here.

In MySQL 5.X you can instead use a variable, initialized with your maximum "group" value, then updated incrementally inside the UPDATE statement, in the 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;

Check the demo here.