Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: Getting rid off duplicates

    I have a huge problem of having many duplicates in a table Streets, so one street name can come up many times but many out of all the duplicates that only one is a real record with other fields/tables filled in.

    Does anybody have a good sql query or example for getting rid of duplicates but also updating the rest of the tables where those fields have an entry?


  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    Select Into newTable distinct * from oldTable
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada

    Select distinct * Into newTable from oldTable | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Careful with that select distinct...

    It sounds like this information is tied to other tables in your db.

    You'd be better advised to run something like this:

    DELETE yourTable
    WHERE NOT IN (SELECT FROM yourTable INNER JOIN yourothertable ON

    Basically that's saying "delete everything in this table that isn't being used in this here other table"

    Edit: To be even more cautions, you could apply the same logic to a "SELECT INTO" statement instead:

    SELECT * INTO yourNewTable
    FROM yourTable INNER JOIN yourothertable ON

    That's applying the opposite logic. Essentially that one says "Squirt everything from yourTable into yourNewTable that has a matching record in yourothertable"
    Last edited by Teddy; 06-24-04 at 09:55.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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