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
