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.

DELETE Table1
WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)

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

: 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.

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.
:

: 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:

select rowid from <table>;

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