Message-ID: <F001.005D63A6.20031110091426@fatcity.com>
Date: Mon, 10 Nov 2003 09:14:26 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Bellow, Bambi" <bbellow@chi.navtech.com>
Subject: Life without a correlated subquery
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Friends --

One of my associates came up to me Friday with a question.  It seemed easy
enough.  I mean, I've been doing stuff like this for years.  The question
was, "I have duplicate ids here, some with X field null, some without.  I
want to get rid of all the duplicates where X field is not null."  Fine.
Standard correlated subquery.

delete from <tablename> a
where rowid not in (select min(rowid)
where pid=a.pid
and X is not null)
and X is not null

Right?

So, my associate says "what are you doing?  you're going to go through the
table every single time for each record?"  Why, yes, that is indeed what I'm
doing here.  "Why don't you just open a cursor and delete that way?" says my
associate...

select pid
from <tablename>
where X is not null
group by pid
having count(*) > 1
{
	skip one
	delete the rest
}


"But," I tell my associate, "you still have to go to the table to get
information on which records to delete.  You can't do this without a
correlated subquery."

Yes, he assures me, there *has* to be a way.

OK.  Maybe I've just been doing things the same way for too long.  I'm
willing to cop to that.  Can anybody out there come up with a way to do this
relatively normal operation without a correlated subquery?

Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
  INET: bbellow@chi.navtech.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@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).