Update Duplicates in SQL Server without a primary key

Question

I need an update statement to resolve some issues with duplicates in a table on SQL Server 2000. The table structure is shown below. There is no primary key in this table. I need a SQL statement that will update the values in cases where there are duplicates by adding 5 to the value until there are no more duplicates.

DocNumber       SeQNumber
Doc001           900
Doc001           900
Doc001           900
Doc001           903
Doc001           904

Desired Result

DocNumber       SeqNUmber
Doc001           900
Doc001           905
Doc001           910
Doc001           903
Doc001           904

This is what I have tried My latest attempt is shown below. In that example I am just adding the counter, but the +5 is what I actually need.

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
With UpdateData  As
(
SELECT  DocNumbr,SeqNumbr,
ROW_NUMBER() OVER (ORDER BY [SeqNumbr] DESC) AS RN
FROM RM10101
)
UPDATE  RM10101 SET SeqNumbr = (select max(SeqNumbr) from RM10101 where docNumbr =          RM10101.DocNumbr and SeqNumbr=RM10101.SeqNumbr) + (@counter)
FROM RM10101
INNER JOIN UpdateData ON RM10101.DocNumbr = UpdateData.DocNumbr 
where rn =@counter   
SET @counter = @counter + 1
end
end

Any help would be much appreciated.
Thanks, Bob

Answer 1

The script might fail in case I have sequence like: ('Doc001',900), ('Doc001',900), ('Doc001',900), ('Doc001',903), ('Doc001',904), ('Doc001',905), ('Doc001',905), ('Doc001',905), ('Doc001',910), ('Doc001',910), ('Doc001',915), ('Doc001',915)

The script will give error as below: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Correct script would be:

WHILE (SELECT SUM(X.A) FROM (  SELECT COUNT(*) A FROM Table_1 GROUP BY DocNumber, SeqNumber HAVING count(*) > 1) X) > 0
BEGIN
   With TableAdjusted as (
        select DocNumber, SeqNumber, row_number() over (partition by DocNumber, SeqNumber order by (select NULL)) RowID
        from Table_1
   )

   update A
     set SeqNumber = (B.RowID - 1)*5 + B.SeqNumber
   from TableAdjusted A
   inner join TableAdjusted B
      on A.DocNumber = B.DocNumber and A.SeqNumber = B.SeqNumber and A.RowID = B.RowID 
   where A.RowID > 1
END