Life without a correlated subquery

From: Bellow, Bambi <bbellow_at_chi.navtech.com>
Date: Mon, 10 Nov 2003 09:14:26 -0800
Message-ID: <F001.005D63A6.20031110091426@fatcity.com>

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_at_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_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 Nov 10 2003 - 11:14:26 CST