-- 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?
Hope this helps, Oleg
16 People are following this question.