Re: Deleting duplicate records

Sorry! Divide by cucumber error!
The statement below should read:
create table new_table as select DISTINCT * from old_table ...

Chrysalis.

Chrysalis wrote:
>
> Kjell R. Christensen wrote:
> >
> > amit srivastava wrote:
> > >
> > > How can one delete all the records and leave one, if you have more than
> > > 2 duplicates for certain records in a table? , using SQL.
> >
> > Hi,
> >
> > this is one way of doing it!
> >
> > sql> delete from 'table_name' where rowid not in (select min(rowid) from
> > 'table_name' group by 'columns that form your unique index'
> >
> > Kjell R.
>
> Another way which leaves the table in a more compact state (if you have
> space) is:
> create new_table as select * from old_table [storage...];
> drop old_table;
> rename new_table to old_table;
Received on Thu Feb 27 1997 - 00:00:00 CST