Re: remote duplicate rows

If you have a primary key value (or OID?) then you can delete the
duplicates in situ using something like (untested)

-- should work if never more than 1 duplicate row for colname1, colname2

delete from table where pk_value in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) > 1
)

-- if you can have multiple duplicate rows for colname1, colname2
-- then you need something like

delete from table where pk_value not in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) = 1
)

Hope that helps.

John

A. Kretschmer wrote:
> am  Wed, dem 13.09.2006, um 15:46:58 -0700 mailte Junkone folgendes:
>> hI
>> i have a bad situation that i did not have primary key. so i have a
>> table like this
>> colname1                colname2
>> 1                                 apple
>> 1                                 apple
>> 2                                  orange
>> 2                                   orange
>>
>> It is a very large table. how do i remove the duplctes quickly annd
>> without much change.
>
> begin;
> alter table foo rename to tmp;
> create table foo as select distinct * from tmp;
> commit;
>
> You should create a primary key now to avoid duplicated entries...
>
>
> HTH, Andreas