deleting duplicate data from a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: deleting duplicate data from a table

  1. #1
    Join Date
    Nov 2007
    Posts
    2

    deleting duplicate data from a table

    hi gud morning to all
    i have accidently copied same data three times in a table when i have disabled my primary key constraints, now nither i am able to enable my primary key constraints nor able to delete the duplicate records.

  2. #2
    Join Date
    Oct 2006
    Posts
    175
    So what can we do for you dear

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    You can find the info by simple googling around.

    Anyway check this http://www.arikaplan.com/oracle/ari71998b.html
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Apr 2007
    Posts
    16
    Hi,
    This happened to me some days ago,and i had to write up a script like this;

    DELETE FROM table
    WHERE rowid not in(
    SELECT MIN(rowid)
    FROM emp3
    GROUP BY col1,col2,col3

    Please ensure that all your columns appear in the group by clause!

    The rowid is a unique field within every oracle table.
    The subquery selects the smallest instance of each record(i.e ,1 out of every 3 instances of your record).
    The main query will delete 2 out of the 3 records returned by the subquery.
    Hope this helps
    Last edited by crimpe; 11-20-2007 at 10:57 AM.

  5. #5
    Join Date
    Nov 2007
    Posts
    2
    DELETE FROM
    WHERE (unique-row-identifier-columns) IN (
    SELECT unique-row-identifier-columns FROM
    GROUP BY unique-row-identifier-columns HAVING COUNT(*) > 1 AND
    ROWNUM < (SELECT COUNT(*) FROM GROUP BY
    unique-row-identifier-columns HAVING COUNT(*) > 1;

    unique-row-identifier-columns -ARE COLUMNS WHICH MAKES A RECORD KEY BASED ON WHICH YOU ARE QUERING THE TABLE AND WHICH ARE DUPLICATE IN THE TABLE.

Posting Permissions

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


Click Here to Expand Forum to Full Width