Re: Updating or deleting rows with self-referential subqueries

What version of IDS do you have ? J. John Bejarano escribis: > I'm well aware of the fact that Informix is unable to > perform an update or a delete statement if it contains > a subquery that refers back to the table that is being > updated or deleted. The universal work-around that > I've seen in the manuals and posted here is to select > the sub-query into a temp table, and then perform the > update or delete based on the contents of that temp > table. > > The problem with that is, it breaks up the SQL into > multiple statements. Other threads can modify the > data between the time of the select and the update or > delete. In our case the subquery may not necessarily > refer to the row being updated, but instead to (for > instance) the max value of one of the columns of the > table. > > How might one set up isolation and locking to allow > the select...into temp and the update or delete to > occur as if it had been one single SQL statement? > Also, if selecting into a temp table and then updating > or deleting in these instances is so widely considered > the work-around, why doesn't Informix implicitly do > this as one operation when it encounters this type of > syntax? That way, no other thread could interfere > between those two operations? I believe this is what > Oracle and SQL Server are doing, because they are able > to handle this syntax without a problem. > > Complicating matters further, we're trying to keep our > code as portable as possible, so altering code > dramatically to facilitate Informix is not an option > here. > > --John Bejarano, > Macromedia, Inc.