Reissue ids to records with duplicates

user_13872143 0
Viewed 99

We have a CRM DB which for the last 6 weeks has been creating duplicate CaseID's

I need to go in and give new case id's int he 20000000 range to all of the duplicates.

So I have found all the duplicates like this

SELECT CaseNumber, 
    COUNT(CaseNumber) AS NumOccurrences
FROM Goldmine.dbo.cases
WHERE CaseNumber > 9000000
GROUP BY CaseNumber
HAVING ( COUNT(CaseNumber) > 1 )

Which brought back this.

query results

I now need to renumber each one of these like so 20000001, 20000002, etc etc

Any help would be great.

sql-query-store sql-server sql
2 Answers

I am going to assume that you are using SQL Server. So you can use updatable CTEs:

WITH dups as (
      SELECT c.*,
             ROW_NUMBER() OVER (ORDER BY CaseNumber) as seqnum
      FROM Goldmine.dbo.cases c
      WHERE CaseNumber > 9000000
     ),
     toupdate as (
      SELECT d.*, ROW_NUMBER() OVER (PARTITION BY CaseNumber ORDER BY CaseNumber) as inc
      FROM dups d
      WHERE seqnum > 1
     )
UPDATE toupdate
    SET CaseNumber = 20000000 + inc;

The first subquery identifies the duplicates by enumerating them. Presumably, you don't want the "first" one to change. So the second CTE selects only the real duplicates and assigns a sequential number. The outer update uses that to assign the new number.

user_11440350

By the look of the data you have got overlaps in numbers because there are records which overlap with the "updated" values if we are to increment by 1. Here is a way to fix this,

with data
  as (select *
            ,count(*) over(partition by x) as cnt
            ,row_number() over(order by x) as rnk
        from t
      )
update data
   set x = x+rnk;

Initial record set

+-----------+
| orig_data |
+-----------+
|  10000009 |
|  10000009 |
|  10000009 |
|  10000009 |
|  10000010 |
|  10000010 |
|  10000011 |
+-----------+

After update

+-----------+
| after_upd |
+-----------+
|  10000010 |
|  10000011 |
|  10000012 |
|  10000014 |
|  10000015 |
|  10000017 |
+-----------+

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c4ea8335abb074b8c0143e2f7c767f04

user_71370900
Related