Table containing duplicates needs a mass update

Question

I've got a table containing data like this

transactionCode	column2	column3	column4	wCode	aCode	column7	column8	column9	column10	liNumber
7938636	2	INVOICE	NULL	1	MZ690577	2021-01-28	NULL	2021-01-28	1	6
7938636	2	INVOICE	NULL	1	MD191807	2021-01-28	NULL	2021-01-28	1	4
7938631	2	INVOICE	NULL	1	MZ320771	2021-01-28	NULL	2021-01-28	1	1
7938631	2	INVOICE	NULL	1	7803A112	2021-01-28	NULL	2021-01-28	4	2
7938576	2	INVOICE	NULL	1	8201A216	2021-01-29	NULL	2021-01-29	1	1
7938598	2	INVOICE	NULL	1	SP046271	2021-01-29	NULL	2021-01-29	1	14

I've also got a script like this which finds the duplicates for me

WITH cte
 AS (SELECT transactionid,
            aCode,
            liNumber,
            wCode,
            RN = Row_number()
                   OVER(
                     partition BY 
                        transactionid, 
                        aCode, 
                        liNumber,
                        wCode
                     ORDER BY 
                        transactionid)
     FROM   duplicates)
SELECT * FROM   cte
WHERE  RN > 1;  

When running that script the data shown is in a format like this..

transactionID	aCode	liNumber	wCode	RN
1012751	DISCOUNT	9	1	2

I can then search for that aCode or transactionID in the duplicates table to see how many there are. So far in my duplicates table, that script returns a total of 34,791 rows. Note, items that have the same liNumber needs to be changed.

My ask is, how do I go about doing this with this large amount of data?

For example,

Transaction 7938636 might have 5 rows. All with the same wCode and the same aCode BUT the liNumber goes up in increments like 1, 2, 3, 4 ect. When a row has the same liNumber; say 1 then that is classed as a duplicate. I then need to update that duplicate row to continue the increments, from 6, 7 , 8 ect.

Does this make sense?

Answer 1

Since the liNumber is not of identical sequence like (1,2,3,4...and so on) you can go with a workaround by updating the duplicates by adding the max of the liNumber with the corresponding rownumber as below.

declare @tbl table(id int, wCode int, aCode varchar(50), liNumber int)

insert into @tbl
values(7938636,1,'MZ690577',1)
,(7938636,1,'MZ690577',1)
,(7938636,1,'MZ690577',2)
,(7938636,1,'MZ690577',3)
,(7938636,1,'MZ690577',8)
,(7938636,1,'MZ690577',9)
,(7938636,1,'MZ690577',9)

declare @maxvalue int = (select max(linumber) from @tbl)

;with cte as
(
select *,ROW_NUMBER()over(partition by liNumber order by id,liNumber) partitionedrn
,@maxvalue + ROW_NUMBER()over(order by id,liNumber)maxx
from @tbl
)
update cte set liNumber = maxx
where partitionedrn > 1

select * from @tbl

Note: This is just a sample data and I did not consider your table to its entirety.