scorpio

Hi newsgroup,

I've encountered a problem with db2/as400.

I need do update rows in a resultset returned from a query "Select * from
table order by field", but it seems that db2/as400 does not support
updatable resultset from select with order by clause.

Did anybody else encounter this problem?
Any suggestions ?

Scorpio.

joe weinstein

Hi. This is expectable. Many order-by queries may actually require the
DBMS to copy the table data to a temporary area to sort, and so the
actual table data is not even returned to the client, but is returned
from that temp copy. It is typically a complicated idea to ask the DBMS
to trace the row from the client to the copy and then back to the original
table row, and they typically don't do it. In fact some DBMSes free the
temp memory holding the sorted data as soon as it is returned to the
client. I suggest you make a separate update statement to change a row,
and use it whenever you find a row in the query that you want to change.
Getting updateable result sets is typically inefficient. Joe Weinstein at BEA

scorpio

"Joe Weinstein" <joeNOSPAM@bea.com> ha scritto nel messaggio
news:4016D4A5.6020103@bea.com...


Thank you for your answer. The problem is I want to lock some rows, and
ordering it's important because i have to put data from "subset" of rows
into different XML document. I'll have to create a more complicated
procedure to lock the data and process them group by group while locks are
mantained.

Scorpio.