RE:

I'm not entirely sure what you're trying to accomplish -- you can get around the error by using "where userid in (" and "servissue in (", but I suspect that's not what you want either -- at least if you want the userid and servissue to come from the same row in the referenced table. (I'm also assuming the primary key is (userid, servissue)). I've had this problem too, (updating based on a composite primary key) and have not found a slick solution. One way is to select the rowid's of the affected rows from bizguaranteereq into a temp table, then update based on those. This is, of course, not a good plan going forward. The other is to select the columns affected into a temp table, update the temp table, and update the original table from that. I'm looking forward to someone showing us both the right way to do this. > -----Original Message----- > From: Astemborski, Jon [SMTP:JAstemborski@biztravel.com] > Sent: Monday, October 02, 2000 8:27 AM > To: 'informix-list@iiug.org' > Subject: > > 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