RE:

update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval, bizguaranteereq where bizgaranteereq.userid=incentval.userid and bizguaranteerq.servissue=incentval.servcode)); update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1)) where exists (select 0 from incentval a1 where bizguaranteereq.userid = a1.userid and bizguaranteereq.servissue = a1.offercode); update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) where userid in (select userid from incentval) and servissue in (select offercode from incentval); begin work; update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) where bizguaranteereq.userid=a1.userid and bizguaranteereq.servissue=a1.offercode; commit work; begin work; update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) from incentval, bizguaranteereq where bizguaranteereq.userid=incentval.userid and bizguaranteereq.servissue=incentval.offercode; commit work; Thanks Richard but I tried the Exists keyword and it gave me the same results as IN(subquery has not returned exactly one row). This would not be a problem if the subset was to be updated with the same static information but it is variable(dependent on the contents of the table I am linking to). As you can see, I have tried just about everything. Thanks for your time. begin work; update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) ; commit work; begin work; update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) where userid in (select userid from incentval) and servissue in (select offercode from incentval); commit work; begin work; update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) where bizguaranteerq.userid=a1.userid and bizguaranteerq.servissue=a1.offercode; commit work; -----Original Message----- From: Richard harnden [mailto:harndr@russelleurope.com] Sent: Monday, October 02, 2000 11:37 AM To: JAstemborski@biztravel.com; informix-list@iiug.org Subject: Re: Something like: update bizguaranteereq set (...) = ((select ...)) where exists (select 0 from incentval a1 where bizguaranteereq.userid = a1.userid and bizguaranteereq.servissue = a1.offercode) ? >>> "Astemborski, Jon" <JAstemborski@biztravel.com> 10/02/00 02:26pm >>> Hello everyone, I am trying to update a table based on the contents of another table but only where the primary keys are equal. Because I can not directly reference the non updated table in a from statement it throws off what I want to do. I tried to replicate it with a sub queries but I received error 284 (a query has not returned not exactly one row). I understand the error, but how can I perform a multirow update based on the contents of another table? (I have tried numerous other variations but to no avail) update bizguaranteereq set (occurdate,itineraryid,statuscd,moddate) = ((select a1.creationdate,a1.itineraryid, 99 statuscd, today moddate from incentval a1, biztemp a2 where a1.userid=a2.userid and a1.offercode=a2.servissue and statuscd!=99)) where userid=(select userid from incentval) and servissue=(select offercode from incentval Regards, Jon Astemborski