Re: using INSERT and REFERENCING the same table via a sub-query?

ldeckard@databank.com (L Deckard) writes: >I am trying to insert rows from a TEMP table into a MSTR table >*only* if *col01 and col02* of the TEMP table DOES NOT exist >in the MSTR table. When I run the the SQL below, I receive an >error from Informix that you cannot INSERT (or UPDATE) a table >that you are *also* referencing in the subquery. I tried to create >a view of the MSTR table and use the view name in the subquery >but, still receive the same error. This works in Oracle and DB2 but >not in Informix. I want to avoid to create another temp table and insert >rows from the MSTR table and run it that way.. Any ideas? Please >email to: ldeckard@databank.com Thanks! Lenny Deckard. how about deleting from the temp table first? if you can't delete, add a "marker" column ... update temp set marker = 'y' where (col01||col02) not in (select col01||col02 from MSTR); Then insert into MSTR where marker = 'Y' -- ----------------------------------------------------------------- Eric Friedman Internet email: efried@netcom.com