47 lines
1.1 KiB
SQL
47 lines
1.1 KiB
SQL
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 |