Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: How can i delete duplicate rows from my table?

    Hi there,
    I had a segmentation fault in the middle of loading huge amounts of data into Oracle and i had to start putting the file of data into the database again. As a result there are some duplicate rows in my table which i'd like to get rid of. I really don't want to scrap everything and start loading the files of data in again so can any one give me suggestions as to how to get rid of these duplicates??

  2. #2
    Join Date
    Sep 2003
    Get a list of the duplicates, then delete those rows where the rownum > 1

  3. #3
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Here's an example ... Concatenate the primary key of the table to the rowid .... I always do a select first ... You can create a table as select
    to get all dups in a temporary table, review them, then delete the
    main table from the rowids ...

    /* Always SELECT before attempting to DELETE !!! */

    SELECT * FROM loader_physician_recs
    WHERE physician_id||rowid IN
    (SELECT a.physician_id||a.rowid
    FROM loader_physician_recs a, loader_physician_recs b
    WHERE a.physician_id = b.physician_id
    AND a.rowid < b.rowid);


  4. #4
    Join Date
    Oct 2003

    Re: How can i delete duplicate rows from my table?

    Create a new table

    CREATE TABLE new_table
    AS (SELECT DISTINCT full_column_list_from_old_table
    FROM old_table);

    this will copy all distinct (non-duplicate) rows.

    truncate the old table.

    Copy back all the records to the old table.

    truncate the new one and drop it.

    next time use a primary key.
    Last edited by stephen_pe; 10-16-03 at 15:26.

Posting Permissions

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