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