deleting duplicate records
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: deleting duplicate records

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi friends,
    I want to know how to delete duplicate records in a table. My table has 3 fields: col1, col2 and col3. I want to delete duplicate records which have same values in col1 and col2.
    Thanks
    manjunath

  2. #2
    Join Date
    Apr 2001
    Posts
    47

    Thumbs up

    Try this:

    delete from your_table a where rowid not in
    (select min(rowid) from your_table b where
    a.col1=b.col1 and a.col2=b.col2);


    Regards,

  3. #3
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Try this

    1) To display duplicate records using the following query

    Select col1, col2, count(*) duplicates
    from tablename
    group by col1, col2
    having count(*) > 1;

    2) To delete duplicate record use the following query

    DELETE from tablename a
    WHERE ROWID NOT IN (SELECT MIN(ROWID)
    FROM tablename b
    where a.col1 = b.col1
    and a.col2 = b.col2);

    Regards


    Santosh

  4. #4
    Join Date
    Oct 2000
    Posts
    211
    thanks a lot, Santosh and Dmitriy

    manjunath

  5. #5
    Join Date
    Jun 2000
    Location
    Chennai/India
    Posts
    90
    check this out please...
    Delete from TableA a where
    A.p_key > (Select min(b.p_key) from TableA b
    Where a.p_key = b.p_key) ;
    which will preserve the last inserted data & remove the older ones
    Delete from TableA a where
    A.p_key < (Select max(b.p_key) from TableA b
    Where a.p_key = b.p_key) ;
    will preserve the first entries and remove the later additions.
    A Winner never Quits, A Quitter never Wins
    Ganesh .R

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by santoshym
    Hi

    Try this

    1) To display duplicate records using the following query

    Select col1, col2, count(*) duplicates
    from tablename
    group by col1, col2
    having count(*) > 1;

    2) To delete duplicate record use the following query

    DELETE from tablename a
    WHERE ROWID NOT IN (SELECT MIN(ROWID)
    FROM tablename b
    where a.col1 = b.col1
    and a.col2 = b.col2);

    Regards


    Hi,

    I had the same issue today where i needed to delete duplicate records and i searched the forum and came across this post.

    I used the able query and it worked great.

    I just wanted to know what steps does the second statement above go through to achieve the objective,

    DELETE from tablename a
    WHERE ROWID NOT IN (SELECT MIN(ROWID)
    FROM tablename b
    where a.col1 = b.col1
    and a.col2 = b.col2);

    Any response would be really appreciated.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  7. #7
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Still waiting....
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  8. #8
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    If two or more rows have similar data it deletes all the rows but one with the lowest rowid.

  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by irehman
    If two or more rows have similar data it deletes all the rows but one with the lowest rowid.
    I know but I am trying to figure out what exactly the query does to delete the duplicate records.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by irehman
    If two or more rows have similar data it deletes all the rows but one with the lowest rowid.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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