Skip site navigation (1) Skip section navigation (2)

Re: duplicate records

From: Devin Ben-Hur <devin(at)ben-hur(dot)net>
To: Thomas J Keller <kellert(at)ohsu(dot)edu>
Cc: pdxpug(at)postgresql(dot)org
Subject: Re: duplicate records
Date: 2006-10-18 23:49:17
Message-ID: 4536BD7D.1000405@ben-hur.net (view raw or flat)
Thread:
Lists: pdxpug
Thomas J Keller wrote:
> I inserted a few hundred records into a new database table and 
> discovered that several where duplicates.
> How does one select these (so they can be deleted)?

delete records, selecting for values duplicated, but excluding the min 
(or max) primary key.  eg:

delete from foo
where pk in (
  select pk
   from
    foo f
    INNER JOIN
    ( -- finds dups and the first pk
     select min(pk) as pk, f1, f2
     from foo
     group by f1, f2
     having count(*) > 1
    ) d
    ON (f.pk <> d.pk) and (f.f1 = d.f1 and f.f2 = d.f2)
)


--
Devin Ben-Hur  503/860-4114  mailto:devin(at)ben-hur(dot)net

"You're entitled to your own opinion, but not your own facts."


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006


In response to

pdxpug by date

Next:From: Selena DeckelmannDate: 2006-10-19 03:44:10
Subject: Re: [Pdx-pm] duplicate records
Previous:From: Vassilis PapadimosDate: 2006-10-18 22:48:44
Subject: Re: duplicate records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group