RE: Deleting Duplicate Rows

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 18 Sep 2000 10:23:24 -0400
Message-Id: <10623.117253@fatcity.com>

How about the following? This is what I use. For a huge table, you may want to do intermittent commits based on the rowcount.

declare

	cursor get_dups is
		select pk_col1, pk_col2, pk_col3, count(*)
		from table
		group by pk_col1, pk_col2, pk_col3
		having count(*) > 1;
	dupRec	get_dups%rowtype;
begin
	for dupRec in get_dups loop
		delete from table
		where pk_col1 = dupRec.pk_col1
		and pk_col2 = dupRec.pk_col2
		and pk_col3 = dupRec.pk_col3
		and rownum = 1;
	end loop;

end;
/

-----Original Message-----
From: Azizulah Khan [mailto:Azizulah.Khan_at_almarai.com] Sent: Sunday, September 17, 2000 12:58 PM To: Multiple recipients of list ORACLE-L Subject: Deleting Duplicate Rows

Hi list,

Do anyone have idea of deleting duplicate rows without using ROWID from the table ????.

TIA Khan  

-- 
Author: Azizulah Khan
  INET: Azizulah.Khan_at_almarai.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Sep 18 2000 - 09:23:24 CDT