[Cialug] SQL wizardry possible?

[Matthew Nuzum]

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