Re: ORDER BY vs. UPDATE

>From: uunet!pmafire.inel.gov!geoff (Geoff Allen) >Subject: ORDER BY vs. UPDATE >Date: 20 Mar 92 22:56:16 GMT >Message-Id: <1992Mar20.225616.13544@pmafire.inel.gov> >X-Informix-List-Id: <news.928> > >I've got a table with a ``priority'' column, and want to be able to >renumber the priorities. For example, if I've got priorities like >1 2 4 5, I want to run the table through my function and get 1 2 3 4. > >The obvious (to me) thing to do was declare a cursor whose select >statement orders the table by priority, and then simply update >the priority column sequentially in a foreach loop on the cursor. > >So, I tried something like: > > ... > > declare c_priority cursor for > select * from pr into pr_rec > where priority is not null > order by priority> for update of priority > > ... > >Unfortunately, I find that I can't have an ``order by'' clause when I'm >going to update. > >Is what I want possible? How do I update in a particular order? Do I >*need* to update in a particular order, or is there something I'm >missing? The reason why ORDER BY and FOR UPDATE are not allowed together is that the ORDER BY may require a temporary table to do the sort, and that would lose the connection between the original table and the rows. So, to answer your question: DEFINE pr_rowid INTEGER, pr_priority INTEGER, new_pr SMALLINT DECLARE c_priority CURSOR FOR SELECT ROWID, Priority FROM Pr WHERE Priority IS NOT NULL ORDER BY Priority LOCK TABLE Pr IN EXCLUSIVE MODE LET new_pr = 1 FOREACH c_priority INTO pr_rowid, pr_priority UPDATE Pr SET Priority = new_pr WHERE ROWID = pr_rowid LET new_pr = new_pr + 1 END FOREACH UNLOCK TABLE Pr Use SHARE mode if other people need access to the table while this is going on, but I'd have to ask what useful results they are going to get? If you have transactions, put a BEGIN WORK before the LOCK table and replace the UNLOCK TABLE with COMMIT WORK. Add error handling to suit yourself/employer. Finally, does it matter if the values stored in the database are not in sequence; as long as the stored priority values satisfy the condition: x < y => x has higher priority than y the selection code will work, despite gaps. Jonathan Leffler (johnl@obelix.informix.com)