Re: Deleting duplicate records

In article <3315A34B.5B60_at_iol.ie>, Chrysalis <cellis_at_iol.ie> wrote:
>Sorry! Divide by cucumber error!
>The statement below should read:
>create table new_table as select DISTINCT * from old_table ...

Actually I'm not sure this will do exactly the same thing. Won't the DISTINCT use every column were as the GROUP BY will only do certain columns. If a row has a duplicate key but different non key fields then the behaviors will be different (I think).

mjr

>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