Re: Deleting duplicate rows

create tmp_view
as
select col1
,col2
,count(*) counter
from table_name
group by col1,col2 ;

delect tablename
where rowid in (select max(rowid) from tablename where (col1,col2) = (select col1,col2 from tmp_view where counter >1 ) ) ;

this command delete only one row

or
delect tablename
where rowid in (select rowid from tablename where (col1,col2) = (select col1,col2 from tmp_view where counter >1 ) ) ;

this command delete all duplicates row

tschwickert_at_nvag.de napisał(a) w wiadomości: <74gkqp$et7$1_at_nnrp1.dejanews.com>...
>Hi,
>
>How can I delete lines appearing more than one time for some columns,
>eg.
>
>COL1 COL2 COL3
>1 a dfgfdg
>2 x dfdsfs
>1 a oiiziz
>
>Line 1 and 3 are identical for COL1 and COL2. I want to eliminate these
>lines.
>Any ideas ?
>(ok, I know how to get the lines which just occurs one, and than can insert
>these into a new table. But is there a way to really delete these lines ?)
>
>Thanks in advance
> Thomas
>
>--
>Thomas Schwickert Phone: +49 69 - 404 8661
>Neckermann Versand AG Fax: +49 69 - 404 5561
>60386 Frankfurt
>Germany
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Dec 07 1998 - 09:48:59 CST