Re: SQL question ...

This is a multi-part message in MIME format. --------------57E89FE415817EF3643EC8D Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit Lin-Chuan Lee wrote: > > Assume I have table A with composite key C1, C2 and > table B with composite key C1, C2 . > > How do I delete from A where C1,C2 in(select C1,C2 from B where XXX) ; > > Assume C1,C2 may not be chars so no || deals ... > also, no cascading deletes ... > > TIA, > -Lin If my wonderful text file will be displayed again without newlines, then please look at http://www.weideneder.de/informix/faq/correl.html Bye Stefan stefan@weideneder.de --------------57E89FE415817EF3643EC8D Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="correl.html" Hi, I saw the example with the correlated subquery. I think it will work if there are just a few rows inside table A. ( Of course it will work with thousnads of rows, but I wonder if you wanna wait for the result. ) Okay, how about SPL ? I've suggested this example a week ago to a friend of mine and it took a very short time ( it was a distributed query using OnLine DS 7.12 on AIX ) begin work; -- if not yet started create procedure dummy();define pk_v anytype; foreach select A.PrimaryKeyA into pk_v from A, B where XXX and A.C1 = B.C1 and A.C2 = B.C2 delete from A where A.PrimaryKey = pk_v;end foreach; end procedure; execute procedure dummy(); drop procedure dummy(); -- if you don't need it any longer commit work; -- do you need a transaction ? No more correlated subquery ! Don't worry, the procedure will be created and removed in a very short time. You will only delete rows matching the condition XXX and A.C? = B.C?. If you don't have a Primary Key, transform the select statement in the foreach loop to: FOREACH SELECT A.C1, A.C2 into c1,c2 FROM A,B WHERE XXX AND ... DELETE FROM A WHERE A.C1 = c1 AND A.C2 = c2;END FOREACH; Bye Stefan stefan@weidender.de --------------57E89FE415817EF3643EC8D--