Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16

    Unanswered: Deleting double records with SQL

    I have an Access-db with approx 5000 records. Many of these records are double and I want to get rid of them. One of those records will have to stay. How can I do that?

    Id code opleidingsnaam
    1 4406 Accountancy
    2 4406 Accountancy
    3 4406 Accountancy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    delete from yourtable
    where id not in
    ( select min(id) from yourtable
    group by code )

    make a backup copy first, just in case (and also to compare to afterwards, to convince yourself it worked)

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Location
    Holland
    Posts
    16
    It does not work. What went wrong?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show the sql, and explain how it didn't work

    thanks

    rudy

  5. #5
    Join Date
    Oct 2002
    Location
    New Delhi, India
    Posts
    2

    Arrow

    Please try it :

    Delete from <Table Name> where count(code) > 1

    Hope that u will get rid of duplicates.
    Thanks.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where count(code)>1 is not valid syntax

    eve, did you try the query i gave you?


    rudy

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    delete from table1 as a
    where id not in (select min(id) from table1 as b where a.code= b.code)

  8. #8
    Join Date
    Aug 2003
    Posts
    1
    Originally posted by r937
    where count(code)>1 is not valid syntax

    eve, did you try the query i gave you?


    rudy
    should be HAVING count(code)>1
    but it deletes without leaving one

    the query rnealejr sent works for me; tx!

Posting Permissions

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