mysql

Update MySQL table with rank number of duplicates by name

In my database, I have three fields of concern: Name, time_dupe, and rank_dupe.

The 'name' field is standardized* but is duplicated many times (dupes are okay).

'time_dupe' is the number of times the name is duplicated. It's attached to each duplicate record [see below].

'rank_dupe' is what I'm try to append to the table. If the record is duplicated 5 time, I want the 'rank_dupe' field to be ordered 1, 2, 3, 4, 5 with the lowest 'id' number being 1.

Here's what the data looks like:

id      name              time_dupe              rank_dupe
66      Joe Bland             4
2622    Joe Bland             4
73      Joe Bland             4
22      Joe Bland             4

99      Mary Fields           2
13      Mary Fields           2

2401    John Saturday         1

What I'd like to end up with:

id      name              time_dupe              rank_dupe
22      Joe Bland             4                      1
66      Joe Bland             4                      2
73      Joe Bland             4                      3
2622    Joe Bland             4                      4

13      Mary Fields           2                      1
99      Mary Fields           2                      2

2401    John Saturday         1                      1

This query will list the records for me in the correct order, but I can't figure out how to change this so it updates the table:

SELECT
  id,
  name,
  ROW_NUMBER() OVER (
    PARTITION BY name
    ORDER BY id
  ) AS row_num
FROM
  myTable

Anybody know how to change this so it adds the 'row_num' to the table field 'rank_dupe'?

Solution

  • Transform your select into a subquery which can be used on an UPDATE ... JOIN method. I am considering id to be unique on the table.

    Use,

    UPDATE myTable m
INNER JOIN (SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
            FROM
           myTable 
      ) m1 on m1.id = m.id
SET m.rank_dupe = m1.row_num

    See example