Results 1 to 2 of 2

Thread: deleting duplicate records

  1. #1
    Join Date
    Jan 2006
    Posts
    1

    deleting duplicate records

    Hi

    Can someone please tell me how to delete duplicate records from my DB, where I have about 65502 records. I have a auto-incremend "id" field, then I have a file_letter field (which holds info like A, B, C - Z), and then I have a file_no field. Basically, the file_letter and file_no fields make a unique pair, thus there can be more than one "file_no = '23' (for ex. file_letter = A, file_no = 32 / file_letter=B, file_no = 32, etc etc)

    How do I delete duplicate records using these two fields as a unique pair? I'm running MySQL 5.0.15-max-log.

    tia

  2. #2
    Join Date
    Jan 2006
    Posts
    6
    Join it on itself. I didn't test this query but the general idea is there.

    DELETE FROM t USING table t, table t2 WHERE t.id > t2.id AND t.file_letter=t2.file_letter AND t.file_no=t2.file_no

Posting Permissions

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