Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: How to get and delete duplicate records in a table...?

    Hi,

    i have a table with some employee details.

    in that table there is duplicate employee for example
    the employee having id 101 having morethan one record..

    so how can i get those duplicate records and delete those records

    thanks in advance...

  2. #2
    Join Date
    Jan 2010
    Posts
    18
    You can find the duplicates with something like the following

    select employeeno, count(*) as empcount
    from employee_table
    group by employeeno
    having count(*) > 1

    Once you've found the duplicates, you can do a SELECT to find all the rows with the same employeeno. Then you can decide which row to delete.

    You need to be careful if you automate this process (you can join the queries) because you'll need to be sure that the process deletes the correct row(s) and leaves the correct row.

    And you definitely want to fix the problem where the duplicates are getting into the table in the first place otherwise you're going to be doing this for the rest of your life.


  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT * FROM employees
    WHERE id IN
    ( SELECT id FROM employees GROUP BY id
    HAVING COUNT(*) > 1 )

    inspect the results and then decide which one you want to delete
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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