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

On Fri, May 21, 1999 at 05:45:00PM +0200, Sebastian Paul Avarvarei wrote: > Hi! > > I recommended: > > UPDATE ISMTMPRPTSTAGE1 SET COUNTTAKENUP = > (SELECT TAKENUP FROM ISMTMPRPTSTAGE2 > WHERE ISMTMPRPTSTAGE1.COMMCODE = ISMTMPRPTSTAGE2.COMMCODE > AND ISMTMPRPTSTAGE1.CAMPCODE = ISMTMPRPTSTAGE2.CAMPCODE)> > And (I checked) it has the correct syntax. The only problem could be that the > subquery does not return EXACTLY one row. So (COMMCODE, CAMPCODE) should be > unique (even primary key). Plese write the exact error message you get. Well, it is not the only problem. The second problem is that this update will set ismtmprptstage1.counttakenup to null for records not in ismtmprptstage2 :-) Best Regards, Octav > > Sebastian Paul A. > E-mail: proteus@romus.com > > > Simon Leigh (21.05.1999 11:26): > > > >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 > > > > > > -- 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