Re: duplicate records

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