Create new unique value in column

Question

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

id	Name	Group
123	Name 1	1
124	Name 2	2
125	Name 3	1
126	Name 4

id is unique and auto-incrementing. 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 of name4 in this example cannot It's 1 or 2.

For example, the result might be:

id	Name	Group
126	Name 4	3

Currently I'm sorting by group descending and just manually inserting the maximum number 1, but I'd like to know if there's a better/faster way to generate new unique values ​​in the column. group has no constraints other than being an integer.

I'm using MySQL Workbench, so I can use SQL commands as well as Workbench-specific options if available.

If anything is unclear, I'll be happy to provide clarification.

Answer 1

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 demo here.

---

In MySQL 5.X, you can use a variable, initialize it with the largest "group" value, and then update it incrementally in a UPDATE statement. >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 demo here.