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.
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:
[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...
Comments
WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)
Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo
: 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.
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.
:
: 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