-360 error
Posted in 2005
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.