sql - Remove redundant rows and update foreign references -

i have sql-table called tblemployees like:

sid primaryid   secondaryid employeeid  employeename       timestamp 1   123         40          1           eastwood, clint    20141016124013 2   123         40          1           eastwood, clint    20141016130043 3   123         40          1           westwood, clint    20141016165733 4   123         40          1           westwood, clint    20141016210205 

and have table called tblemployeedata column employeeidentificataion references column sid of tblemployees.

the table tblemployeedata looks like

sid   employeeidentificataion    data 86    4                       [binary data] 89    2                       [binary data]      90    1                       [binary data]  104   3                       [binary data]  

now need remove redundant rows in tblemployees , update references in tblemployeedata youngest entry in tblemployees. identify youngest can use timestamp. identify duplicates have use columns primaryid, secondaryid, employeeid , employeename.

background of problem in our application every time employee-data added new record added tblemployees. need know if employeename changes. unfortunately we're not able check changing name before inserting new record.

i able in c# sql-connection. unfortunately have in sql because of performance-aspect.

can give me hint or how start on issue?

here can task :

;with cte_todelete      (select *,                 row_number()                   on (                     partition primaryid, secondaryid, employeeid, employeename                     order sid) rn            tblemployees) delete cte_todelete  rn > 1  

row_number() assign incremental number (starting 1) each group of primaryid, secondaryid, employeeid, employeename ascending order of sid. , delete cte delete rows except first 1 of group.

note : replace order sid desired criteria row should remain in table order timestamp desc or order timestamp

edit : run script @ once change references , delete redundant records :

if( object_id('tempdb..#temptable') not null )     drop table #temptable;  select *,        row_number()          on (            partition primaryid, secondaryid, employeeid, employeename            order timstamp desc) rn   #temptable   tblemployees  update r set    r.employeeidentificataion = t2.sid   tblemployeedata r        join #temptable t1          on r.employeeidentificataion = t1.sid        join #temptable t2          on t1.primaryid = t2.primaryid             , t1.secondaryid = t2.secondaryid             , t1.employeeid = t2.employeeid             , t1.employeename = t2.employeename             , t1.sid <> t2.sid             , t2.rn = 1  delete m   tblemployees m        join #temptable t          on m.sid = t.sid  t.rn > 1; 

check in sql fiddle here.