Duplicate Value need to fix by using Update statement

Transact-SQL
,
1

Hi All,
I've a 'contract' table and the 'Contract Number
shoudn't be duplicate I need to replace the the max value where the 'ContractID' is max or min. Table below

So the issue in the contract are 'C123' and 'C124' for ContractId 2 and 4 are duplicates and would like to replace the Max(ContractNum) for ContractID = 2 by max(ContractNum) to 'C126' and for ContractID = 4 will be replace Max(ContractNum) to 'C127'
Hope it make sense.

Existing values are duplicates:

ContractID ContractNum
1 C123
2 C123
3 C124
4 C124
5 C125

Required Result:
ContractID ContractNum
1 C123
2 C126 - the Max() values of the tblContract table
3 C124
4 C127 --the MAx() value of the tblContract table
5 C125

The desire output should be like as follows
I've use the following SQL but doesn't help work

update tblContract a SET a.ContractNum =
(Select Max(b.contractNum)
from tblcontract b
where b.ContractNum = a.ContractNum
)
WHERE a.ContractNum in
(
select b.ContractNum --as Duplicate
from tblcontract b
where Deleted = 0
AND b.ContractNum ='C23868'
group by b.ContractNum
having count(*) > 1
)

2

Perhaps something like this:

with cte1
  as (select contractid
            ,row_number() over(partition by contractnum order by contractid) as rn
        from tblcontract
       where deleted=0
     )
    ,cte2
  as (select contractid
            ,row_number() over(order by contractid) as rn
        from cte1
       where rn>1
     )
    ,cte3
  as (select cast(substring(max(contractnum),2,len(max(contractnum))-1) as int) as maxnum
        from tblcontract
     )
update a
   set a.contractnum='C'+cast((c.maxnum+b.rn) as varchar(8))
  from tblcontract as a
       inner join cte2 as b
               on b.contractid=a.contractid
       cross apply cte3 as c
;
3

You're star its working on my follwoing sample data I will test again before I pass on Live database
Thanks again Bitsmed you 5 star from me.

Blockquote
drop table Dup_Contract;
create table Dup_Contract
(
ContractID integer,
ContractNum VARCHAR(10),
ContractName VARCHAR(20)
)

insert into Dup_Contract values (1, 'C23868','LAX');
insert into Dup_Contract values (2, 'C23868','LAX');
insert into Dup_Contract values (3, 'C23868','Birmingam Inv');
insert into Dup_Contract values (4, 'C23869','Birmingam Inv');
insert into Dup_Contract values (5, 'C23871','Birmingam LAX');

select * from Dup_Contract;