IDS Forum

-360 error

Posted By: John Bejarano
Date: Tuesday, 14 June 2005, at 7:06 p.m.

I know that in versions of Informix through v9.4, you
could not update a table using a subquery that
referenced that table. If you tried that, you'd get
the famous "360: Cannot modify table or view used in
subquery." error.

It seems that most if not all other DBMS's allow this.
I know SQL Server does, and I believe that Oracle and
PostgreSQL do as well, but I'm not positive about
those two.

As it happens, my shop is trying to port some code to
work with Informix, but this code contains many, many
updates with subqueries like this. Enough, that it's
a serious time, money, and effort issue.

I know that the standard workaround is to select data
from the table, insert it into a temp table, and
update the original table based on the contents of the
temp table. However, as we're using a connection
pool, and a cluster of application servers, it becomes
a pain to keep track of what temp tables are still in
use in transactions, and which can and should be
dropped. Even if we could resolve all of those
issues, for future manageability considerations, we're
looking to avoid branching off too far from the
original code base, so the temp table workaround is
being greeted rather icily by my developers.

My questions are these: A) Is this restriction still
true in Informix v10, or does v10 finally allow
updates with subqueries referencing the updated table?
And B) If the restriction is still in place, is
there some other workaround, documented or
undocumented, and preferably doable in a single SQL
statement, that we could use to produce this effect?

This one problem is causing a large amount of
heartburn directed at Informix in my organization, and
any help to alleviate that would be very welcome.

Thanks very much

--John Bejarano.

IDS Forum is maintained by Administrator with WebBBS 5.12.