Re: Query: For update cursors

On Mon, 22 Feb 1999, SHAHIDA ABUBAKER wrote: >Is it possible to declare a cursor ( for update ) for a query >that is based on a multi-table join? No. >If yes, then are rows from all >tables involved in the query locked after a fetch ? N/A. The normal trick is to decide which tables you need to lock, and then run a non-update cursor on the multi-table join, probably with an ORDER BY clause, and then run separate, single-table selects with locking for the exact row in each table that needs to be locked, typically using the primary key fields to identify the row. DECLARE c_select CURSOR FOR SELECT ... FROM Table1, Table2 WHERE ... ORDER BY ... DECLARE c_upd_tab1 CURSOR FOR SELECT * FROM Table1 WHERE PKCol1 = pkval1 ... FOR UPDATE FOREACH c_select INTO x.* LET pkval1 = x.pkcol1 FOREACH c_upd_tab1 INTO y.* -- do update -- this loop body executes just once! END FOREACH END FOREACH Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn