use EnVisage

begin tran;

with duplicates([Id]
      ,[FirstName]
      ,[LastName]
      ,[IsActiveEmployee]
      ,[ExpenditureCategoryId]
      ,[StartDate]
      ,[EndDate]
      ,[EmployeeID]
      ,[Email]
      ,[WorkWeekId],
	  [Rn]) as (select pr.*, ROW_NUMBER() over (partition by pr.Email order by pr.Id) as Rn 
from PeopleResource pr
where email is not null)
update pr
set pr.email = dp.Email + cast (dp.Rn as nvarchar(3))
from PeopleResource pr
	inner join duplicates dp on dp.Id = pr.Id
where dp.Rn > 1;

with duplicates([Id]
      ,[UserName]
      ,[PasswordHash]
      ,[SecurityStamp]
      ,[Discriminator]
      ,[Email]
      ,[Phone]
      ,[Type]
      ,[PreferredResourceAllocation]
      ,[PreferredTotalsDisplaying]
      ,[FirstName]
      ,[LastName]
      ,[LastLoginDate]
      ,[LoginDate],
	  [Rn]) as (select usr.*, ROW_NUMBER() over (partition by usr.Email order by usr.Id) as Rn 
from AspNetUsers usr
where email is not null)
update usr
set usr.email = dp.Email + cast (dp.Rn as nvarchar(3))
from AspNetUsers usr
	inner join duplicates dp on dp.Id = usr.Id
where dp.Rn > 1

commit tran