Assign unique group id for multiple rows that has the same id-CodePudding

So I kinda realized it too late but I need to have another column on one of my tables that has the group id of each row. The data looks like this:

Code Description Group(currently empty)
1    Test
2    Test
1    Test
3    Test
3    Test

What the column group contain should be

Code Description Group(currently empty)
1    Test        56
2    Test        57
1    Test        56
3    Test        58
3    Test        58

Can this be done through UPDATE? Thank you in advance.

CodePudding user response：

If the version of the database is 8.0 , then you can use a window function such as

UPDATE t AS t0
  JOIN
  ( SELECT t.*, 55   DENSE_RANK() OVER (ORDER BY Code, Description) AS rnk
      FROM t ) AS t1
    ON t0.Code = t1.Code
   AND t0.Description = t1.Description
   SET t0.`Group` = rnk

Demo

CodePudding user response：

For Mysql versions prior to 8.0, we can use a user variable to generate the numbers. Here is the code written and tested in workbench.

create table test(Code int, Description varchar(10), `Group` int);

insert test values(1,'TEST',null),
(2,'TEST',null),
(1,'TEST',null),
(3,'TEST',null),
(3,'TEST',null);
-- this is the derived result table we would like to have, which is created using the user varible trick to generate required numbers
select code,@row_id:=@row_id 1 as row_id
from (select distinct code
     from test) tb ,
     (select @row_id:=55) t ;

-- update the base table using the info from the derived table     
update test t1,
    (select code,@row_id:=@row_id 1 as row_id
    from (select distinct code
         from test) tb ,
         (select @row_id:=55) t ) t2
set t1.`group` = t2.row_id
where t1.code=t2.code
;

select * from test;

-- result set:
# Code, Description, Group
1, TEST, 56
2, TEST, 57
1, TEST, 56
3, TEST, 58
3, TEST, 58