Re: SQL question ...
Scott Rubin wrote:
>
> Here is a way that should work for engines with pre-fragmented tables:
>
> select A.rowid as row_id
> from A, B
> where A.C1 = B.C1
> and A.C2 = B.C2
> and XXX
> into temp t1 with no log;>
> delete from A
> where rowid in (select row_id from t1);>
> ==================================================================
> Scott Rubin, FourGen Software (Client Services)
> scottr@fourgen.com
This is probably the fastest method, but what if you're not using
rowid's and the only unique key in table A is (C1,C2)?
Hasn't anyone else ever wished there could be syntax like:
delete from A
where (C1,C2) in (select C1,C2 from B)
I asked Informix about this once long ago; never got an answer(like
i expected one...). Would it be that hard for them to implement?
I have almost always ended up using temp tables like the above
rather than correlated subqueries in most SQL statements because
theyre almost always faster.
--
Sincerly,
Doug Wilson(dougw@amgen.com)
*** The opinions expressed here are my own and not neccessarily ***
*** anyone elses ****
*** Disclaimer: These are the opinions of the poster not Amgen Inc.***