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;
>
>
A couple potential problems here. First, you forgot to drop table tmp. But maybe that is good thing because although
theOP hasn't told us anything else useful about the situation, and he has clearly contrived a simplistic facsimile of
hisreal problem, to be useful the table most likely either has foreign key references, and/or is the primary key for
othertable foreign keys. You're suggestion will break whatever application this data base supports because all the
foreignkeys will point to table tmp rather than foo afterwards. Similarly, there is the problem of any indexes on the
tablethat would be lost. But I suppose one can make the point that your suggestion is a great solution, given the
contrivedexample and insufficient problem understanding presented by the OP -- I really think he needs more help than
herealizes.
Regards,
Berend Tober
