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

OK, Bob. What have you already tried? Please share your efforts with us, even if they were not successful. — PM 77-1, Aug 26 '14 at 01:39
(Link 1)[http://stackoverflow.com/questions/18137809/how-to-update-multiple-duplicates-with-different-values-on-the-same-table] (Link 2)[http://stackoverflow.com/questions/17783906/update-one-of-2-duplicates-in-an-sql-server-database-table] — ah_hau, Aug 26 '14 at 01:50
according to the documentation, the ROW_NUMBER() function was added in SQL Server 2005. Which version of SQL Server are you using? — Jeff Ogata, Aug 26 '14 at 02:05
Thanks for the response patrick. I am sorry if I wansn't clear in my post, but I need to update the seqnumbr, based on duplicates in the docnumbr. — Chaos, Aug 26 '14 at 02:07
It's SQl 2000. I am developing on a later version and did not know that about the row number. — Chaos, Aug 26 '14 at 02:26

score 0 · Answer 1 · answered Aug 26 '14 at 01:55

0

;with tb1(id, seqNUm, docNum) as (
SELECT  ROW_NUMBER() OVER (ORDER BY SeQNumber), DocNumber, SeQNumber From table1
)

update tb1 set docnum=201 where id=2

set id whatever row you want to update

answered Aug 26 '14 at 01:55

Patrick Guimalan

990
9
11

score 0 · Answer 2 · answered Aug 26 '14 at 03:05

This is a start, I'm having a hard time converting this select query into an update statement.

--Build your tally table and sample data

if object_id('dbo.tally') is not null 
        drop table dbo.tally

select top 11000 
    identity(int,1,1) as n
into tally
from master.dbo.syscolumns sc1,
    master.dbo.syscolumns sc2

create table #test_table (
    DocNumber varchar(100),
    SeqNumber int
)

insert into #test_table
select 'Doc001', 900 union all
select 'Doc001', 900 union all
select 'Doc001', 900 union all
select 'Doc001', 903 union all
select 'Doc001', 904

select 
    t1.DocNumber,
    t1.SeqNumber,
    NewSeqNumber =  t1.SeqNumber + (5 * (t.n - 1))
from (
    select
        *,
        c = count(*)
    from #test_table
    group by
        DocNumber, SeqNumber
)t1
inner join tally t
    on t.n <= t1.c

drop table #test_table

Nizam · Answer 3 · 2014-08-26T13:09:43.493

A bad case would be

('Doc001',900), ('Doc001',900), ('Doc001',900), ('Doc001',903), ('Doc001',904), ('Doc001',905), ('Doc001',905), ('Doc001',905), ('Doc001',910), ('Doc001',910), ('Doc001',915), ('Doc001',915)

when you update the rows from 900 to 905 and 910, then you have a case of repetition that didn't exist before.

To solve it, use the following query:

WHILE EXISTS (SELECT * FROM Table1 GROUP BY DocNumber, SeqNumber HAVING count(*) > 1)
BEGIN

   With TableAdjusted as (
        select DocNumber, SeqNumber, row_number() over (partition by DocNumber, SeqNumber order by (select NULL)) RowID
        from Table1
   )

   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

select * from Table1 order by DocNumber, SeqNumber

SQL Fiddle

Kasim Husaini · Accepted Answer · 2014-08-26T09:52:20.260

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

Thanks for commenting my script. I have updated mine to use `EXISTS` instead of `COUNT` which will make the query faster. — Nizam, Aug 26 '14 at 13:14

Update Duplicates in SQL Server without a primary key

4 Answers4