Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

sql for deleting duplicate rows

Raj19Raj19 Posts: 4Member
Hi,
How can I delete duplicate rows , keeping one copy of each, from a table which has no primary / unique key. My idea is to get rid of redundant records which were inserted unchecked in the beginning.

Comments

  • gigsvoogigsvoo Posts: 328Member
    DELETE Table1
    WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)

    Neo Gigs
    http://communities.msn.com.my/VisualBasicwithNeo

  • MrEdMrEd Posts: 180Member
    : DELETE Table1
    : WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)
    :
    : Neo Gigs
    : http://communities.msn.com.my/VisualBasicwithNeo
    :
    :

    umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:

    delete from table1 a
    where rowid > (
    select min(rowid)
    from table1 b
    where a.keys = b.keys
    );

    I think this is oracle specific though.
  • ryan_j_smithryan_j_smith Posts: 6Member
    This isn't going to accomplish what you want and it is Oracle specific.
    You have to first decide what columns in the table make a duplicate row and then join on ALL of those columns ( in the example col1 and col2 ). This solution is Oracle specific. If you need it for another database you will need to place a primary key on the table ( which makes the query easier ) and then only delete those rows where one primary key is greater or less than another.

    delete from MyTable o
    where
    exists (
    select * from MyTable i
    where
    o.col1 = i.col1 and
    o.col2 = i.col2 and
    ... and
    o.rowid < i.rowid )
    );

    : umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:
    :
    : delete from table1 a
    : where rowid > (
    : select min(rowid)
    : from table1 b
    : where a.keys = b.keys
    : );
    :
    : I think this is oracle specific though.
    :

  • sumedhsumedh Posts: 103Member
    : This isn't going to accomplish what you want and it is Oracle specific.
    : You have to first decide what columns in the table make a duplicate row and then join on ALL of those columns ( in the example col1 and col2 ). This solution is Oracle specific. If you need it for another database you will need to place a primary key on the table ( which makes the query easier ) and then only delete those rows where one primary key is greater or less than another.
    :
    : delete from MyTable o
    : where
    : exists (
    : select * from MyTable i
    : where
    : o.col1 = i.col1 and
    : o.col2 = i.col2 and
    : ... and
    : o.rowid < i.rowid )
    : );
    :
    : : umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:
    : :
    : : delete from table1 a
    : : where rowid > (
    : : select min(rowid)
    : : from table1 b
    : : where a.keys = b.keys
    : : );
    : :
    : : I think this is oracle specific though.
    : :

    just get the 'rowid' of the duplicate rows u want to delete:

    [code]
    select rowid from ;
    [/code]

    and then use the DELETE command to remove a row with that rowid.....
    stupid solution.....but it works......
    dont do this if u have a lot of records...

    -sumedh


Sign In or Register to comment.