Something like this should work. Please notice how I posted ddl and sample data in a consumable format so this is easy to work with. You should do this on future questions. 😀

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

MyName varchar(10)

)

insert #Something

select 'John' union all

select 'John' union all

select 'Tom' union all

select 'Bob' union all

select 'Bob' union all

select 'Bob' union all

select 'Mike';

with MyCTE as

(

select *

, ROW_NUMBER() over (partition by MyName order by MyName) - 1 as RowNum

from #Something

)

update MyCTE

set MyName = MyName + case when RowNum = 0 then '' else CAST(RowNum as varchar(2)) end

select * from #Something