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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Selena Deckelmann | 2006-10-19 03:44:10 | Re: [Pdx-pm] duplicate records |
Previous Message | Vassilis Papadimos | 2006-10-18 22:48:44 | Re: duplicate records |