Removing Duplicates in SQL Server 2008

Question

This is my second attempt at this. I am trying to update data in a SQL table where there are duplicates to resolve the duplicate.

Data Structure

DocNumbr     SeQnumbr     Dex_Row_Id
CRM025222    32768            1
CRM025222    32768            2
CRM025222    16384            3

Desired Result

DocNumbr     SeQnumbr     Dex_Row_Id
CRM025222    49152            1
CRM025222    32768            2
CRM025222    16384            3

For each duplicate, I need to update the seqnumbr to be the max value of seqnumbr for the duplicates by Docnumbr + 16384. Dex_row_ID is a primary key and is unique.

My Attempt

Begin
Declare @count as integer = (SELECT COUNT(*) AS DUPLICATES
FROM            dbo.RM10101
GROUP BY DocNumbr, SeqNumbr
HAVING        (COUNT(*) > 1))

Declare @counter as integer =1
While @Counter < @count


begin



Update RM10101 set SeqNumbr= (select MAX(dex_row_id) FROM RM10101 E2
WHERE E2.DocNumbr = RM10101.DocNumbr AND E2.SeqNumbr = RM10101.SeqNumbr) + (16384+) 
WHERE dex_row_id < (select MAX(dex_row_id) FROM RM10101 E2
WHERE E2.DocNumbr = RM10101.DocNumbr AND E2.SeqNumbr = RM10101.SeqNumbr
) 
SET @counter = @counter + 1
END
end

I have tried the code above, but the value being used to add to 16384 is the max seqnumbr of all rows, I need it be the max for the set of duplicates. I am not a dba, but am being pressed into duty on this and am really lost on how to get the join or subquery right. This is being done in SQL Server 2008. Originally I was told it was 2000, so if there is a better way using a cte or some other new method, I would be open to it. Any help would be much appreciated. Thanks, Bob

possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Oliver, Aug 27 '14 at 15:27

Felix Pamittan · Accepted Answer · 2014-08-27T13:52:49.833

2

-- Build Test Data
create table #test_table(
    DocNumber varchar(255),
    SeqNumber int,
    Dex_Row_Id int
)

insert into #test_table
select 'CRM025222', 32768, 1 union all
select 'CRM025222', 32768, 2 union all
select 'CRM025222', 16384, 3

-- Solution: Start
;with cte as(
    select
        *,
        rn = row_number() over(partition by DocNumber order by SeqNumber desc)
    from #test_table
)
update cte 
    set SeqNumber = SeqNumber + 16384
where 
    rn = 1     
-- Solution: End

select * from #test_table

-- Drop test data
drop table #test_table

edited Aug 27 '14 at 13:52

answered Aug 26 '14 at 23:55

Felix Pamittan

31,544
7
41
67

Thanks for your response, but I am not sure how to sure this. how would I get all the rows into the #test_table. The data that I gave is just a sample, there many different values. – Chaos Aug 27 '14 at 13:09
That was just a test data. Replace #test_table with your real table. I put comments on the code to show which part is the solution. – Felix Pamittan Aug 27 '14 at 13:50
Thanks again for your help. I think this is close. It seems like your solution is updating a non-duplicate row. If my data looks like: Docnumbr SeqNumbr Dex_Row_ID doc1 147457 12 doc2 147457 13 doc1 147457 14 The result is Docnumbr SeqNumbr Dex_Row_ID doc1 163841 12 doc2 163841 13 doc1 147457 14 Thanks! – Chaos Aug 27 '14 at 15:00

Removing Duplicates in SQL Server 2008

1 Answers1