Re: Ordered update cursors?

esa@cyclone.atl.ga.us writes: |> According to the Informix SQL reference and tutorial manuals, the |> select statement associated with an update cursor may not include |> an "order by" clause. I'm curious as to why and what workarounds |> are possible to achieve the same effect. As to the why: The ANSI Standard states that a cursor with an order by can be read only (ANSI X3.135-1968, 8.2 Syntax rule 4). In this case, we are following the standard. |> I suspect that a "where" clause involving an indexed column on a |> priority field would cause the rows to be presented in index |> sequence, but is it safe to rely on that? Not entirely. If the table is small, the cost based optimizer will sometimes decide that a sequential scan is more efficient (as you only need 1 read to fetch a row, vs. 2 reads for an indexed access). You could fetch a rowid along with the rest of the data, do an order by with a regular cursor and combine it with a cursor stability isolation level (I assume you are using logging). This will place a share lock on the row as it is fetched. You can then use the rowid to update the row easily. The flaw is that it cannot be guaranteed that the person who first put the lock on will be the first to perform an update (actually, multiple shared locks would block the update). Dave