Question 113262 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Assign unique group id for multiple rows that has the same id

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search