Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Question Unanswered: Deleting Duplicate Rows in SQL Table

    I have an SQL tables [Keys] that has various rows such as:
    [ID] [Name] [Path] [Customer]
    1 Key1 Key1 InHouse
    2 Key2 Key2 External
    3 Key1 Key1 InHouse
    4 Key1 Key1 InHouse
    5 Key1 Key1 InHouse

    Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
    [ID] [Name] [Path] [Customer]
    1 Key1 Key1 InHouse
    2 Key2 Key2 External


    I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

    Any help would be greatly appreciated.
    Thanks,
    Last edited by Shaitan00; 11-16-07 at 14:26.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since you want nothing left in your table (see your example above), a simple TRUNCATE TABLE should do nicely!

    -PatP

  3. #3
    Join Date
    Jan 2006
    Posts
    13
    Pat: Funny Sorry I posted that too quick, updated it now.
    Thanks,

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh, so now you get picky about things!

    Try this:
    Code:
    DELETE FROM myTable
       WHERE EXISTS (SELECT *
          FROM myTable AS foo
          WHERE  foo.[name] = myTable.[name]
             AND  foo.[path]= myTable.[path]
             AND myTable.[id] < foo.[id]
    -PatP

Posting Permissions

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