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
Received on Fri Jul 25 2003 - 08:26:51 CDT
