Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: How to find duplicate records in a table?

    I'm trying to create a primary key on a table but it won't work because
    there are duplicates in the table.

    Does anyone know how to write a query that would retrieve the duplicate
    records that match a certiain criteria?

    For example, if table a contains columns b, c, and d I want
    to retrieve all the duplicate rows using b and c as the search criteria.

  2. #2
    Join Date
    Mar 2003
    Posts
    12

    Re: How to find duplicate records in a table?

    Originally posted by brewerd
    I'm trying to create a primary key on a table but it won't work because
    there are duplicates in the table.

    Does anyone know how to write a query that would retrieve the duplicate
    records that match a certiain criteria?

    For example, if table a contains columns b, c, and d I want
    to retrieve all the duplicate rows using b and c as the search criteria.
    Hi,

    Try this query to show you the duplicated rows:

    select b,c,count(*)
    from
    x
    group by b,c
    having count(*) > 1;

    Hope, it helps
    Regards.

  3. #3
    Join Date
    Jul 2003
    Posts
    15
    Thanks, that's what I was looking for.

  4. #4
    Join Date
    Jul 2003
    Posts
    1
    the thought 'running before you can walk' springs to mind. I guess there was no primary key beforehand and you are doing your best to fix it, if you have found yourself in that awful position I would strongly recommend www.dbdebunk.com.
    Mac
    Last edited by alimac; 07-31-03 at 21:58.

  5. #5
    Join Date
    Aug 2003
    Posts
    1
    Once you've retrieved them you'll want to delete the duplicates. The SQL statement below is an example that will delete duplicate entries from the example EMP table:

    delete
    from emp e1
    where exists (select null from emp e2
    where e2.empno = e1.empno
    and e2.rowid > e1.rowid)
    /

    This find records that have the same primary key (empno) but are different records (rowid)

    Richard Graham

Posting Permissions

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