Re: "UPDATE" format, need help

In article <DEsr4A.F5J@nntpa.cb.att.com> rjc@cbnews.cb.att.com "robert.cook" writes: > 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 Firstly, you can't have an ORDER BY in a FOR UPDATE cursor. The two are mutually exclusive. Secondly, you can't (to the best of my knowledge) have a FOR UPDATE if you're selecting from multiple tables. (Huge stab in the dark here, my manuals are +- 10000 km away!) Thirdly, you don't need this. > 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 I would handle it as follows: DECLARE fupd_curs CURSOR FOR SELECT nc_contact_id, nc_log_dt, nc_log_tm, FROM metcls ORDER BY nr_contact_id FOREACH fupd_curs INTO upd_rec UPDATE metrcv SET nr_frcv_dt = upd_rec.nc_log_dt, nr_frcv_tm = upd_rec.nc_log_tm WHERE upd_rec.nc_contact_id = nr_contact_id END FOREACH HTH, Spokey the Wheeler------------------------------------------------------------- "Billy Boy" - das aufregende andersche kondom!