Re: Simple Delete Gets Complex..any Ideas

Are you trying to do: DELETE FROM Table_A WHERE Table_A.PK IN (SELECT TA.PK FROM Table_A TA, Table_B TB WHERE TA.PK = TB.FK ); Or do you need to do a full correlated subquery: DELETE FROM Table_A WHERE Table_A.PK IN (SELECT TA.PK FROM Table_A TA, Table_B TB WHERE TA.PK = TB.FK AND TA.PK = Table_A.PK AND ... ); You may still run into problems because I'm not sure that Informix allows the sub-query to reference the table which is the target of the DELETE operation. If that's the case, then the normal technique is to build a temp table and then use that: SELECT Table_A.PK FROM Table_A, Table_B WHERE Table_A.PK = Table_B.FK AND ... INTO TEMP T; DELETE FROM Table_A WHERE PK IN (SELECT * FROM T); DROP TABLE T; You'd need to consider whether you should lock the table, or simply set the isolation level appropriately, and you'd probably do the whole thing inside a transaction, etc. Informix doesn't support the '(+)' notation to mean outer join; if you need an outer join, you'll need to investigate the reference manuals for the syntax you require. You'll also need to be very careful. And I'm not clear why you'd be deleting the rows in the master table (Table_A) when there are still references in the detail table (Table_B) where you will end up with violated FK->PK referential constraints. I assume that's because we aren't seeing the whole example... If you aren't using Informix, your answer will be different, but the question then arises -- why ask the Informix news group? Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I decline to respond to messages with anti-spam in the return path. >From: "John C. Pugh" <cpugh@mindspring.com> >Date: Tue, 15 Jul 1997 12:51:45 -0700 >X-Informix-List-Id: <news.40443> > >Objective: > >Deleting rows from table "Table_A" based on results of a subquery which >requires an additional table "Table_B". > >current syntax: > >Delete from Table_A where> >(select * from Table_A, Table_B where >Table_A.PK = Table_B.FK (+)) > >needless to say this doesn't work, I also attempted the following > >Delete from Table_A where >EXISTS >(select * from Table_A, Table_B where >Table_A.PK = Table_B.FK (+))> >If my subquery returned one row, Table_A was history (LoL..thank God for >RollBacks)!! > >I also tried: > >Delete from >(select * from Table_A, Table_B where >Table_A.PK = Table_B.FK (+)) > >But this attempted fails to "SPECIFY" exactly which table to apply the >delete method to. > >I know in Sybase you would do something like > >delete Table_A from Table_A, Table_B where Table_A*=Table_B > >Basically, I'm attempting to delete rows from Table_A based on a criteria >that requires the presence of Table_B. > >Anyone out there have any ideas????? > >Thanx In Advance... >Tony P.