FW: [Cialug] SQL wizardry possible?

[murraymckee at wellsfargo.com]

Creating a view will still result in the query running 3 times.

Create TEMP_DELETES Table as <BIG MESS>;

delete from turba_objects where turba_objects.owner_id in
TEMP_DELETES.owner_ID; => 
delete from horde_histories where history_who in TEMP_DELETES.owner_ID;
=> 
delete from horde_prefs where pref_uid in TEMP_DELETES.owner_ID;

Drop table TEMP_DELETES;

Murray McKee 
Data Analyst
LI Data Modeling, Mapping, and Analysis
Wells Fargo Lending Information Systems - TIG (Technology Information
Group)
Mail: 1 Home Campus
MAC X2301-03G
Des Moines, IA 50328 
WORK (515)324-4689 Cell (515) 559-4390  FAX (515) 324-4452
Location: 800 S. Jordan Creek Parkway
West Des Moines, IA 50266
MurrayMcKee at WellsFargo.com 
"This message may contain confidential and / or privileged information.
If you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein.  If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message.  Thank you for your cooperation."

-----Original Message-----
From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On
Behalf Of Matthew Nuzum
Sent: Wednesday, October 31, 2007 11:01 AM
To: Central Iowa Linux Users Group
Subject: Re: [Cialug] SQL wizardry possible?

On 10/31/07, Daniel A. Ramaley <daniel.ramaley at drake.edu> wrote:
> My next challenge is for each pref_uid returned by that query, delete 
> it from 3 different tables. So for example, if 'dar012' is one, then i

> need to run these 3 queries:
>
> => delete from turba_objects where owner_id='dar012'; => delete from 
> horde_histories where history_who='dar012'; => delete from horde_prefs

> where pref_uid='dar012';
>
> I suppose i could issue something like this (where <big mess> is the 
> large select statement):
>
> => delete from turba_objects where owner_id in ( <big mess> ); => 
> delete from horde_histories where history_who in ( <big mess> ); => 
> delete from horde_prefs where pref_uid in ( <big mess> );
>
> Of course if i went that route, deleting from horde_prefs would have 
> to be last since that's what the query is pulled from. But, is there a

> more efficient way to do it, rather than running the select 3 times?

If you'll do this often, you can create a view that presents your data
as a table. So do:

create view expired_pref_uid as <big mess>;

Then, delete from turba_objects where ownerid in (select pref_uid from
expired_pref_uid);

--
Matthew Nuzum
newz2000 on freenode
_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug