Re: ORDER BY vs. UPDATE

In article <1992Mar20.225616.13544@pmafire.inel.gov> geoff@pmafire.inel.gov (Geoff Allen) writes: -- about how he needs to update a column based on the current -- values in that column, selected in ordered sequence. The main reason you can't update an ordered cursor is practical. Most engines implement most order-by's by creating a temp table. Upon OPEN, they select all the desired rows, sort them into a temp table, and set up to return the first. In fact, Informix OnLine will often be able to use an index to deliver rows in sorted order without making a temp table. You might think, OK, why can't I update in that case? But what if the updated value now has a new place in the ordered sequence? Should the engine return that row again when its new value comes up in the sequence? If not, how is it to know the difference between an "original" row and an "updated" row, when it gets to that row later on? I concur with the response posted by jfr@locus.com (Jon Rosen), especially his caution about concurrent access. It's probably best to LOCK this TABLE while you work on it, if there are other users around. But there are always two ways to do anything in 4GL and SQL, so here's mine. To me this looks like a legitimate use for the ROWID. DEFINE pr_id, pr_val INTEGER SELECT ROWID, priority FROM PR ORDER BY priority INTO TEMP pr_temp DECLARE ctemp CURSOR FOR SELECT * FROM pr_temp FOREACH ctemp INTO pr_id, pr_val -- calculate your new priority UPDATE pr SET priority = -- whatever WHERE ROWID = pr_id END FOREACH Basically this makes explicit what the engine was probably doing anyway, but it eliminates all question of the updated row values interfering with the sort order.