RE: SQL question ...
>How about
> DELETE FROM A WHERE
> C1 IN (SELECT C1 FROM B WHERE XXX)
> AND C2 IN (SELECT C2 FROM B WHERE XXX);
No, that will delete the wrong data. For example, given the data:
A(C1, C2) = (12, 24)
B(C1, C2) = (12, 19)
B(C1, C2) = (37, 24)
and the condition XXX as '1=1', the proposed DELETE would delete the row
from A, even though there isn't proper match. You probably have to use a
correlated sub-query like:
DELETE FROM A
WHERE EXISTS (SELECT *
FROM B
WHERE A.C1 = B.C1 AND A.C2 = B.C2 AND XXX);
That does the trick, but it isn't going to be very fast. I tested it with
XXX as '1=1', with the data shown, and also with an extra row in B:
B(C1, C2) = (12, 24)
and it worked correctly in both cases. I was actually using OnLine version
7.20.UC1 on Solaris 2.4, (though neither the platform nor the version is
important for this example).
There might be some other fancy trick you can pull, but I have neither the
time nor the inclination to investigate that.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>From: CSC CIS <spal@watson.den.csci.csc.com>
>To: "'Lin-Chuan Lee'" <lclee@nntp1.best.com>
>Date: Tue, 1 Oct 1996 15:59:36 -0600
>X-Informix-List-Id: <list.11571>
>
>Lee
>
>How about
> DELETE FROM A WHERE
> C1 IN (SELECT C1 FROM B WHERE XXX)
> AND C2 IN (SELECT C2 FROM B WHERE XXX);>
>Sujit Pal
>DBA, Computer Sciences Corp.
>
>----------
>From: Lin-Chuan Lee[SMTP:lclee@nntp1.best.com]
>Sent: Tuesday, October 01, 1996 1:15 PM
>Subject: SQL question ...
>
>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 ...