Solved

Make duplicate codes unique

Posted on 2013-12-07
2
257 Views
Last Modified: 2013-12-09
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.
DupCode.xlsx
0
Comment
Question by:Easwaran Paramasivam
2 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 39702918
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)
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 39705591
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

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 47
Sql to find top3 for each record 7 18
Help with SQL Query 23 39
replication - alerts? 4 19
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now