Re: ORDER BY vs. UPDATE

In article <1992Mar26.223306.7532@pmafire.inel.gov> geoff@pmafire.inel.gov (Geoff Allen) writes: >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) I suppose priority is unique for each row. If not, the above solution will assign different priorities to rows of same old priorities. If priority is unique, then even there is no job_id, the priorities can also be updated by using priority itself as the primary key: my solution for the last part: let new_pr = 1 > foreach c_priority foreach c_priority > update update > set priority = pr_rec.priority set priority = new_pr > where current of c_priority where priority = pr_rec.priority > let new_pr = new_pr + 1 let new_pr = new_pr + 1 > end foreach end foreach