SQL Server rename duplicate entries-CodePudding

I have a table (TableA) with primary key (dummy_id) containing a lot of duplicate invoice numbers (check_no) Duplicates can MAXIMUM be up to 6 I want to keep one unchanged and rename the rest having A,B,C,D,E in the left part

Example

dummy_id    check_no
11537       1/41034             
11539       1/41034             
11542       1/41034             
11545       1/41034             
11516       1/41034             
11486       1/41034

should become

dummy_id    check_no
11537       1/41034             
11539       A/41034             
11542       B/41034             
11545       C/41034             
11516       D/41034             
11486       E/41034

Any ideas more than welcome

Thank you in advance

CodePudding user response：

Basically what you can do is assign a row_number in a CTE and then update the CTE, using stuff to replace the first character with the required sequence.

In the example below, it keeps the row with the lowest dummy_id unchanged and updates the rest. Adjust this ordering criteria if necessary.

with r as (
  select *, Row_Number() over(partition by check_no order by dummy_id) rn
  from t
)
update r set
  check_no = Stuff(check_no, 1, 1, Char(rn   63))
where rn > 1;

Demo Fiddle