Re: "UPDATE" format, need help

(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)) No, I don't have a fetish for double parentheses -- they are a neccessary part of the syntax. And No, I haven't actually run this through any program or tested it in any way, so there could be an unexpected gotcha, but I think that will work as it is. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: rjc@cbnews.cb.att.com (robert.cook) }Date: Tue, 12 Sep 1995 14:33:45 GMT }X-Informix-List-Id: <news.16918> } } I and several others have experienced problems trying to use the UPDATE }function in 4gl. Any assistance is appreciated. } } I am trying to update a date & time field in one table with the date & time }data of another table where the ticket numbers are the same. } }DECLARE fupd_curs CURSOR FOR }SELECT nc_contact_id, nc_log_dt, nc_log_tm, } nr_contact_id, nc_frcv_dt, nc_frcv_tm }FROM metcls, metrcv }WHERE nc_contact_id = nr_contact_id }ORDER BY nr_contact_id -- There may be multiply nr_contact_id's, but } only one nc_contact_id } }FOR UPDATE } FOREACH fupd_curs INTO upd_rec } } UPDATE metrcv } SET nr_frcv_dt = metcls.nc_log_dt, } nr_frcv_tm = metcls.nc_log_tm } WHERE CURRENT OF fupd_curs } END FOREACH } }Not real exact, but I can't get it to work following any of the examples in the }book. Also, there are no examples showing joined tables. } }TNKS, Robert