Re: ORDER BY vs. UPDATE

In article <1992Mar20.225616.13544@pmafire.inel.gov> geoff@pmafire.inel.gov (Geoff Allen) writes: > >This is potentially a bonehead question, but I've been unable to answer >it with my available documentation, so... There are NO bonehead questions concerning SQL... No one COMPLETELY understands it yet :-) >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. > >[example munched] Unfortunately, you can't ORDER BY an updatable cursor. >Unfortunately, I find that I can't have an ``order by'' clause when I'm >going to update. Ah, you figured that out :-) >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? Well, ANYTHING is possible :-) The question is how much control do you have over the table and is concurrency with other possible transactions, particularly updates, a requirement while you are doing the renumbering? If it is (i.e., if you need to permit others to access and/or update the table while you renumber), the problem is very difficult. If on the other hand, you can get private access to the table, it is relatively simple. Your approach is correct, you just have to get rid of the updatable cursor (the UPDATE WHERE CURRENT OF). As long as your table has a primary key (one or more columns that uniquely define each row) and as long as the rank column IS NOT part of the primary key, you just do this: open cursor as select key1,key2... from table order by priority do for all rows fetch from cursor into vars update table set priority = :nextpriorityvalue where key1 = :keyvar1 and key2 = :keyvar2 ... end This has the same effect as an updatable cursor but without the order by limitation. You can also read out the whole table to a regular file, do the reranking, erase the existing table and reinsert all the rows. No, its not pretty, but then neither is SQL :-) Jon Rosen