Re: ORDER BY vs. UPDATE

I wrote a question about a table which includes a priority column, which I wanted to update to put into a ``gapless'' order. Several people posted solutions (some quite elaborate!) and a few mailed me. Here's what I ended up doing: OLD NEW --- --- define define pr_rec record like pr.*, pr_rec record like pr.*, new_pr smallint new_pr smallint let new_pr = 1 let new_pr = 1 declare c_priority cursor for declare c_priority cursor for select * from pr into pr_rec select * from pr into pr_rec where priority is not null where priority is not null order by priority order by priority for update of priority foreach c_priority foreach c_priority let pr_rec.priority = new_pr let pr_rec.priority = new_pr update update set priority = pr_rec.priority set priority = pr_rec.priority where current of c_priority where job_id = pr_rec.job_id let new_pr = new_pr + 1 let new_pr = new_pr + 1 end foreach end foreach So the difference was just a matter or removing the updatable cursor, and updating the row based on a unique identifier (several folks suggested rowid, but I already had a unique job id, so I just used it) rather than the cursor location. Thanks to all for your help! -- Geoff Allen \\ Driggs, Idaho -- cultural hub of the west! uunet!pmafire!geoff \\ geoff@pmafire.inel.gov \\ (Tom Harper in rec.skiing)