RE: Update with a correlated subquery - YUCCH

---------- Jacob Salomon (jake@garpac.com) wrote home to the family and said, amongst other things: >I, Jacob Salomon, asked for help: >> I am hoping someone can come up with a better SQL than this. >>[...] >Some folks tried [...]. Others (like Apostolos) pointed out that >this is a situation for a stored procedure. [...] >BTW, in 12 years of 4GL programming (I used release 1 in 1986) I have >never before run across a rather simple restriction: I cannot use an >ORDER BY when preparing a SELECT .. FOR UPDATE statement. Perhaps this >was lifted after 4.1x but I rather doubt it. The limitation is there in all versions of Informix, in ESQL/C and SPL as well as I4GL. The reason given is that the sort implied by the order by means that the SELECT is working on a copy of the data, not the original records. The restriction is part of ANSI SQL. The normal technique for dealing with it is to select the primary key info for the data plus whatever is needed to sort it correctly using a non-UPDATE cursor, and then to use the PK info to fetch each row in turn with a FOR UPDATE cursor. Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>