Re: Find out duplicate keys

Vito Kwan wrote:
>
> Hi all,
> I wonder if there is a easier way to delete the rows with duplicate
> keys (including single PK and composite PKs) without using resursion or
> alias of the table.
> Thanks for your help.
>
> Vito Kwan
> vkwan_at_dorsai.org
>
> --
> Vito Kwan
> vkwan_at_dorsai.org
> ======================================================================
> I am Sir Oracle, And when I ope my lips, let no dog bark!
> Shakespeare: The Merchant of VeniceSure. The simplest approach is an anonymous block like this...

declare

cursor dupes is

	select keyval
		from tablename
		group by keyval
		having count(*) > 1;

begin

	for dupe_rec in dupes
	loop
		delete
			from tablename
			where tablename.keyval = dupe_rec.keyval;
	end loop;

This approach works fine for composite keys also.

If you know there can be only one dupe, and you don't care which one gets deleted. A really sleazy one liner is:

delete

	from tablename
	where rowid in
	(
		select min(rowid)
			from tablename
			group by keyval
			having count(*) > 1
	)

hope this helps,

Fred Received on Sun Apr 07 1996 - 00:00:00 CEST