Re: Re[2]: Union quries: INTERSECT, MINUS, etc

Love it classic execution of KISS, no bull it just works and quick and efficient

Waiting for my plane home from Perth
Have a good weekend as its Friday night here

Cheers

--
=================================================
Peter McLarty               E-mail: Peter.Mclarty_at_mincom.com
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                            Facsimile: +61 (0)7 3303 3048
=================================================
"If people did not sometimes do silly things, nothing intelligent would 
ever
get done." 
   - Ludwig Wittgenstein
=================================================
Mincom "The People, The Experience, The Vision"

=================================================

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






Rachel Carmichael <wisernet100_at_yahoo.com>
Sent by: ml-errors_at_fatcity.com
26-07-2003 12:24 AM
Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Re[2]: Union  quries: INTERSECT, MINUS, etc


okay, I answered this offlist but...

it started out as "do we have a problem, indicated by records in the
parent table with no children"

select id from parent
minus
select parentid from child

that identified that we had a problem.

next step (I'm a paranoid DBA when it comes to permanently deleting
data from production)

create holding_table 
as select * from parent
where id in 
(select id from parent
minus select parentid from child)

last step

delete from parent where id in select id from holding_table

elegant? no. Fast? yes. And when I'm doing this 10 minutes before I'm
supposed to leave for the day, fast is what I want :)


I'm a BIG believer in "plain vanilla" coding. Slick is fine, but if I
have to spend too much more time than the slick query saves me in
creating it, it's not worth it. This is a one-off.


--- Jonathan Gennick <jonathan_at_gennick.com> wrote:


> Friday, July 25, 2003, 6:39:35 AM, you wrote:

> RC> not very slick but I used MINUS yesterday to find parents with no

> RC> children so as to purge them.... 

> 

> Offhand, I'd think you could do this without using MINUS.

> Maybe I'm wrong. But assuming there is a non-MINUS solution,

> what led you to choose to use MINUS?

> 

> Best regards,

> 

> Jonathan Gennick --- Brighten the corner where you are

> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

> 

> Join the Oracle-article list and receive one

> article on Oracle technologies per month by 

> email. To join, visit

> http://four.pairlist.net/mailman/listinfo/oracle-article, 

> or send email to Oracle-article-request_at_gennick.com and 

> include the word "subscribe" in either the subject or body.

> 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: Jonathan Gennick

>   INET: jonathan_at_gennick.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).



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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