delete duplicate records

Results 1 to 2 of 2

Thread: delete duplicate records

  1. #1
    Join Date
    Dec 1969

    Default delete duplicate records

    I need to delete all records that have the same entry in a particular column. <BR><BR>I know I can do Select Distinct to select unique records but how do I delete the left overs as it were....or is there a much better way to do it? <BR><BR>I&#039;m sure this must be a common trick to perform <BR><BR>Many thanks, Chris

  2. #2
    Join Date
    Dec 1969

    Default I have often used...

    ...the ORDER BY clause to group the records together.<BR>Then, as you iterate through the recordset, check to see if the current record is the same as the last record. If so, add the current records primary key to a comma delimited string.<BR><BR>Of course, you&#039;ll have to have a variable for last record and<BR>current record. Initializing last record = current record on the very first record in the recordset.<BR><BR>If you are comfortable using the GetRows() on the recordset to<BR>turn it into an array, you could just reference previous elements in the array. Just depends on what you are most comfortable with.<BR><BR>After iterating through the complete recordset, issue a delete statement similar to the following by stripping off the last<BR>comma from the string:<BR><BR> sql = "delete from tableA "<BR> sql = sql & "where uniqueid in (" <BR> sql = sql & Trim(Mid(sKeys,1,Len(sKeys)-1)) & ")"<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts