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

Posted in 1999

Octav Chiriac — Fri, 21 May 1999 17:56:54 +0300 — source: Informix-list mailing list archive (1991-1998)

On Fri, May 21, 1999 at 10:26:56AM +0100, Simon Leigh wrote: > > 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 > Try it: UPDATE "sm".ISMTMPSL SET (Counttakenup) = ((SELECT f.takenup FROM "sm".ISMTMPL2 f WHERE f.CommCode = "sm".ISMTMPSL.CommCode AND f.CampCode = "sm".ISMTMPSL.CampCode)) WHERE EXISTS (SELECT f1.takenup FROM "sm".ISMTMPL2 f1 WHERE f1.CommCode = "sm".ISMTMPSL.CommCode AND f1.CampCode = "sm".ISMTMPSL.CampCode) I hope it will work, Octav -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com