RE: Deleting Duplicate Rows

From: Abdul Aleem <abchaudhary-ho_at_beaconhouse.edu.pk>
Date: Mon, 18 Sep 2000 12:24:04 +0500
Message-Id: <10623.117226@fatcity.com>

Dear Khan,

I tried the solution after I had written to you, sorry that DELETE part was wrong, it will delete all the records. The modified one follows. Hope you would not mind the syntax as I am in the phase of learning Oracle Syntax these days.

Suppose your table Salaries having PK on EmpNo and Salary_Date, your main cursor would be

CREATE CURSOR All_Duplicate
AS
SELECT EmpNo, Salary_Date, SUM(1) AS Cntr FROM Salaries GROUP BY EmpNo, Salary_Date HAVING Cntr > 1;

The second cursor "DUPS" would be a parameterized, it would be based on the current record of All_Duplicate cursor. This will have FOR UPDATE clause too.

CREATE CURSOR Dups .....

   WHERE .... Open All_Duplicate cursor

LOOP

	FETCH FROM cursor
	EXIT WHEN cursor.NOTFOUND
	Open the second cursor, and delete the record using

:SYSTEM.CURSOR_RECORD = n (1, 2, 3, 4)

END LOOP; Hope this would help.

Aleem
 -----Original Message-----

From: 	Sandeep Kurliye [mailto:Sandeep.Kurliye_at_almarai.com] 
Sent:	Monday, September 18, 2000 11:52 AM
To:	abchaudhary-ho_at_beaconhouse.edu.pk
Subject:	RE: Deleting Duplicate Rows

Hi,

Can you please elaborate the point. I didn't get you. Thanks in Advance,
Regards,
Sandeep.
-----Original Message-----

	From:	Abdul Aleem [SMTP:abchaudhary-ho_at_beaconhouse.edu.pk]
	Sent:	Monday, September 18, 2000 9:05 AM
	To:	Multiple recipients of list ORACLE-L
	Subject:	RE: Deleting Duplicate Rows

Dear Khan,
You need to create a cursor with the primary key of the table plus a column
SUM(1) AS cntr, group by the primary key and add HAVING Cntr > 1. Now use
DELETE WHERE [Primary key] IN SELECT( PK from the cursor) HTH!
Aleem

-----Original Message-----

	Sent:	Sunday, September 17, 2000 9:58 PM
	To:	Multiple recipients of list ORACLE-L
	Subject:	Deleting Duplicate Rows

Dear Khan,
You need to create a cursor with the primary key of the table plus a column
SUM(1) AS cntr, group by the primary key and add HAVING Cntr > 1. Now use
DELETE WHERE [Primary key] IN SELECT( PK from the cursor) HTH!
Aleem

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).


	-- 

Author: Abdul Aleem
INET: abchaudhary-ho_at_beaconhouse.edu.pk

	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 Received on Mon Sep 18 2000 - 02:24:04 CDT