Re: delete duplicate rows
Ruchi Patel (.Chris_Swecker@avid.avid.com) wrote:
:
: Here is another way you can remove the duplicates.
: DELETE FROM table_name WHERE ROWID NOT IN
: (SELECT MIN(ROWID) FROM TABLE GROUP BY column_name)
Careful, that won't work! A table that you DELETE from cannot be used in
the sub-SELECT list.
You need to do it in two steps:
select min(rowid) tmp_rowid from _tablename_
group by _key_fields_
into temp tmp_table
with no log;
delete from _tablename_
where rowid not in
(select tmp_rowid from tmp_table);
Catalin.
--
===========================================================================
Catalin Badea _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ _/_/_/
_/ _/ _/ _/ _/_/ _/
Senior Technical Architect _/ _/_/ _/ _/_/_/ _/ _/_/_/
_/ _/ _/ _/ _/ _/
Tecsys Inc. _/ _/_/_/ _/_/_/ _/_/_/ _/ _/_/_/
====== cat@tecsys.com =========== include system "disclaimer.4gh" ======