Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Delete duplicate records in table

    Hi All,

    I have a table in SQL server 7 where all the records(rows) have been duplicated i.e. all rows have atleast 3 entries in the same table plus the table missed out the primary key. Now i wanted to delete all the duplicate records from the table at the same time one entry for the same (duplicate) row should remain in the database.
    Can anyone help me out.

    ExpertCalling

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "plus the table missed out the primary key" mean?

    how many columns are in this table? can you show some sample rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You can try this:

    1) Rename the table with the duplicates.
    2) Create a new empty table with primary key enabled.
    3) Insert 'DISTINCT' from old table -- or:

    Code:
    INSERT INTO New_Table
    SELECT * FROM Old_Table O
     WHERE NOT EXISTS (
           SELECT 1 FROM New_Table N
            WHERE N.Primary_Key = O.Primary_Key)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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