Re: SQL question ...

Douglas Wilson <dougw@amgen.com> wrote: :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) Yes this syntax should be allowed. The problem is big enough that Informix ought to add it, and go to the SQL standard commity with it if it isn't allready part of the standard. :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. This is so true. The use of exists as suggested by many is hardly usefull in most cases. It's extremly slow if there are more than a few rows in the tables. If this use of exists could have been made fast by Informix the above syntax *may* not be needed. To solve this problem we have gone to the length of adding a serial as an extra unique key to some tables where we need to do this kind of thing. It's very bad to have to do that! Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org