RE: Why doesn't this query work part 2...

I am not sure of the intended logic of your query. But try the following. At least the syntax should be ok. update ismtmpsl set counttakenup = (select takenup from ismtmpsl2 f where ismtmpsl.commcode = f.commcode and ismtmpsl.campcode = f.campcode) where commcode in (select commcode from ismtmsl2 f1 where f1.campcode = ismtmpsl.campcode); Note that a table alias cannot be used in the "update" clause. Alanoly. -----Original Message----- From: Simon Leigh [mailto:simon.leigh@btinternet.com] Sent: Friday, May 21, 1999 5:27 AM To: informix-list@iiug.org Subject: Why doesn't this query work part 2... Thanks guys for your answers, but none seemed to solve the problem. The fact is that the last query was definately wrong, and in fact it was written to replace the query below which works in sql anywhere and access, but not many other places apparently! What I need is a replacement for this that works everywhere, particularly informix ( as I am supposed to be the informix expert! ... Help!) NB. I want to avoid creating temp tables. UPDATE sm.ISMTMPSL e INNER JOIN sm.ISMTMPSL2 f ON (e.CommCode = f.CommCode) AND (e.CampCode = f.CampCode) SET e.Counttakenup = f.takenup Thanks in advance Simon