Hope this helps, Oleg
-- we need to update the values in the Employee column which will force -- the engine to rebuild the index anyway, so lets drop it now explicitly alter table dbo.Old_Table drop constraint PK_Old_Table; go -- update the Employee column with new values from Merge_Table and also -- mark the "duplicate" records which need to be deleted from the table ;with records as ( select ot.Employee, ot.[Year], ot.Comment, mt.New_Code, row_number() over (partition by mt.New_Code, ot.[Year] order by ot.Employee) N from dbo.Old_Table ot inner join dbo.Merge_Table mt on ot.Employee = mt.Old_Code ) update records set Employee = New_Code, Comment = case when N > 1 then 'TO BE DELETED' else Comment end; -- now deleted the "duplicates", i.e. records which originally belonged to -- different emplolyeee which was then merged into one delete from dbo.Old_Table where Comment = 'TO BE DELETED'; go -- finally, restore the index alter table dbo.Old_Table add constraint PK_Old_Table primary key clustered (Employee, [Year]); go -- check the data: select * from dbo.Old_Table; Employee Year Comment ---------- ---- -------------------------- EMPLOYEE1 08 Hope 2008 is a great year. EMPLOYEE1 09 Another year. EMPLOYEE1 10 Maybe the last?
16 People are following this question.