sql for deleting duplicate rows - Programmers Heaven

Howdy, Stranger!

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

Categories

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.