Someone recently re-imported records that were already in my table to the same table and created duplicate, triplicate and quadruplicate records. The relevant columns in the table look like this:detCellNo detMsg detDateCommitted--------- ------ ----------------The only way I can know a record is a repeat is by detDateCommitted which is the surest because it is an exact match for any repeated records. I want to delete quadruplicates, truplicates and duplicates such that I remain with only record one of each. How can I achieve that?This is what I tried to do. for the quadruplicates, I did DELETE tblMyTable from (SELECT top 3 detCellNo, detMsg, detDateCommitted from tblMyTable WHERE detOfImport >= '[HERE I PASS THE DATE THE IMPORT WAS DONE, NOT SURE IF ITS NECESSARY]' GROUP BY detCellNo, detMsg, detDateCommitted having count=4) as t1 Where tblMytable.detDateCommitted = t1.detDateCommitted
I was to repeat this for triplicates, changing top 3 to top 2 and count=4 to count=3. and the same for duplicates. It didnt work.Can someone show me the most effecient way of doing this?
DELETE duplicate rows
Posted - 2006-03-27 : 10:44:28
