Make duplicate codes unique

Hi Experts,

  Please do refer the attached document. Some codes are duplicate here.

I look for update query to make the duplicates as unique.

Say for example:

If the codes are duplicate as: QAIN0001, QAIN0001, QAIN0001
and the expected update as QAIN0001, QAIN0002, QAIN0003

Please help.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
Assuming your Code values are 8 characters and the last 4 are digits and you will always start with 1 when reassigning the new values then this worked on a test table I created with your sample data.  It only updated the ones that were complete duplicates and left the others alone.
;with cte as (
  select row_number() over (partition by substring(Code,1,4) order by ID) rn, count(*) over (partition by Code) cnt, *
  from BadCodes
update cte
set Code= substring(Code,1,4) + right('000' + cast(rn as varchar),4)
WHERE cnt > 1;

Open in new window

Ephraim WangoyaCommented:
you can use row_number function to update your records

;with cte as (
  select row_number() over (order by updatedfield) rn, *
  from yourtable
update cte
set updatedfield = 'QAIN000' + cast(rn as varchar)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.