Re: "UPDATE" format, need help

Malcolm, I agree that there is a correlated sub-select, and that these should be avoided if a non-correlated statement can be used instead. However, I don't think that a non-correlated sub-select will work this time. The question is, which takes longer: (1) the UPDATE executing solely in the engine with no traffic back and forth between the program and the engine, or (2) SELECTING each row and returning the data to the application and then executing the singleton UPDATE, with data flying in both directions, even allowing for the UPDATE to be prepared just once and executed thereafter. Even without considering a network connection, there is at least a some chance that the sub-select will be quicker than the loop version. I'd suggest that if the correlated sub-query update isn't faster than the loop, then the optimizer has blown it badly! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: onlinedbc@cix.compulink.co.uk ("Malcolm Weallans") }Date: Fri, 15 Sep 1995 17:56:57 GMT }X-Informix-List-Id: <news.17018> } }Although I hate to disagree with someone as experienced as Jonathon }Leffler I feel I should warn everybody. The soultion he is proposing }involves the use of a correlated sub-query. These can be notoriously }inefficient. In the example the subselect would have to be performed for }every record in metrcv. Not a good idea?? } }> In article <434g8g$sit@cssun.mathcs.emory.edu> }> johnl@informix.com "Jonathan Leffler" writes: }> > > (1) You cannot have ORDER BY clauses in FOR UPDATE cursors. }> > (2) You cannot have joins in FOR UPDATE cursors. }> > (3) You should be able to do the whole thing in one statement, which }> > should be more cost-effective -- less traffic between application }> > and database. }> > > > UPDATE metrcv }> > SET (nr_frcv_dt, nr_frcv_tm) = }> > ((SELECT metcls.nc_log_dt, metcls.nc_log_tm }> > FROM metcls }> > WHERE metcls.nc_contact_id = }> metrcv.nr_contact_id)) }> > The master has spoken! :-) And as elegant as eloquent! }> > Cheers, }> > Spokey the }> Wheeler------------------------------------------------------------- }> > "Billy Boy" - das aufregend andere kondom!