Re: contracting tables

This is complicated (and I haven't tested it) but it might work:

delete from <yourtable> where oid in (select (select oid from <yourtable>
where <field>=yt.<field>) from <yourtable> yt group by <field>)

Pretty crazy - don't know if it will work!

Chris

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Markus Bertheau
> Sent: Friday, 30 November 2001 3:53 PM
> To: Peter T. Brown
> Cc: postgres sql list
> Subject: Re: [SQL] contracting tables
>
>
> On Fri, 2001-11-30 at 00:50, Peter T. Brown wrote:
> > I have a table with many records, some of which are duplicates
> (there is no
> > unique constraints). How can I contract this table to remove
> any of these
> > duplicate records? Like when using GROUP BY in a select
> statement, except
> > that I want to just remove the extra entries from this table
> directly... Any
> > ideas?
>
> If I understood you right, one problem is to identify only one row of a
> duplicate. There is a 'hidden' field oid in every table that is unique.
> It goes like this:
>
> select oid, * from <yourtable>
>
> So you could delete the rows by oid:
>
> delete from <yourtable> where oid = <oid>
>
> HTH
>
> Markus Bertheau
>
>
>