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'?
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