CODEWITHSUNDEEP
sqlsql-server
user1063108
user1063108

Reputation: 712

Update Duplicate value in second row if found

I have come across a problem. I have a table where some rows will have one column data duplicated. However other columns are unique. What I am after is when it detects that this is the second row and a previous row might already have the same column value, in that instance just add some number to that columns value.

if object_id('tempdb..#tempt') is not null drop table #tempt;
create table #tempt(
  cmpName varchar(50),
  cmpCode varchar(50)
)
insert into #tempt select 'cmp-ABC234FG Ont','252750023862545';
insert into #tempt select 'cmp-XDDF34FG Ont','252750057762511';
insert into #tempt select 'cmp-POC624AG Ont','252750057762789';
insert into #tempt select 'cmp-ABC234FG Ont','252750057762511';

cmpName             cmpCode
cmp-ABC234FG Ont    252750023862545
cmp-ABC234FG Ont    252750057762511
cmp-POC624AG Ont    252750057762789
cmp-XDDF34FG Ont    252750057762511

in the above example int first 2 rows column cmName has duplicate values. What I am after is the second row should have any char appended to its value so that it will become a unique row .eg.

cmpName             cmpCode
cmp-ABC234FG Ont    252750023862545
cmp-ABC234FG Ont-2  252750057762511

Can some kind soul help me with this? Thanks in advance

Upvotes: 0

Views: 136

Answers (1)

BJones
BJones

Reputation: 2460

As astentx stated, you'll need a column to order by or decide which row you want to update. In this example I used cmpCode:

if object_id('tempdb..#tempt') is not null drop table #tempt;
create table #tempt(
  cmpName varchar(50),
  cmpCode varchar(50)
)
insert into #tempt select 'cmp-ABC234FG Ont','252750023862545';
insert into #tempt select 'cmp-XDDF34FG Ont','252750057762511';
insert into #tempt select 'cmp-POC624AG Ont','252750057762789';
insert into #tempt select 'cmp-ABC234FG Ont','252750057762511';

;with cte as(
select row_number() over(partition by cmpName order by cmpCode) rn,
        *
from #tempt
)

update cte
set cmpName = concat(cmpName,'-',rn)
where rn > 1

select *
from #tempt

This will take into consideration there might be more than two records with the same cmName value.

Upvotes: 1

Related Questions