Search code examples
mysqlsql-update

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.

Solution

  • 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