create table test (id int identity primary key, val varchar(20) )
	--id is a pk for the cursor so it can update using "where current of"

-- name a is not duplicated
-- name b is duplicated 3 times
-- name c is duplicated 2 times

insert test values('name a')
insert test values('name b')
insert test values('name c')
insert test values('name b')
insert test values('name b')
insert test values('name c')

begin tran; -- Computed table expressions require the statement prior to end with ;

with cte(val,row) as (

	select val, row_number() over (partition by val order by val) row
	--partiton is important. it resets the row_number on a new val
	from test
	where val in ( -- only return values that are duplicated
		select val
		from test
		group by val
		having count(val)>1
	)
)
update cte set val = val + ltrim(str(row))
--ltrim(str(row)) = converting the int to a string and removing the padding from the str command.

select * from test

rollback

begin tran

declare @row int, @last varchar(20), @current varchar(20)
set @last = ''
declare dupes cursor
	for
	select val
	from test
	where val in ( -- only return values that are duplicated
		select val
		from test
		group by val
		having count(val)>1
	)
	order by val

	for update of val

open dupes
fetch next from dupes into @current
while @@fetch_status = 0
begin
	--new set of dupes, like the partition by in the 2005 example
	if @last != @current
		set @row = 1

	update test
		--@last is being set during the update statement
		set val = val + ltrim(str(@row)), @last = val
		where current of dupes

	set @row = @row + 1

	fetch next from dupes into @current
end
close dupes
deallocate dupes

select * from test

rollback

update mytable set mycolumn = concat(mycolumn, id)
            where id in (<select duplicate records>);

ALTER TABLE <table_name> ENABLE NOVALIDATE UNIQUE;