Re: Simple Delete Gets Complex (What if delete becomes modify???)

MRSW wrote: > > While reading this thread, this question pops to my mind! What if we > want > to update some field in Table_A based on some fields in Table_A and > Table_B? > Of course we can write a script or a ESQL/C program, but can we do it > in > a SQL stmt??? All the creative souls all there please help! :-) I take it your not reading c.d.i. This was answered by Jonathan Leffler. I'll cross post his reply in the hope that: a) He doesn't reply to you directly. b) He is too busy to mail bomb me. :-) " 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. " Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+