Re: "Cannot modify table" error message
Andres Maehlmann wrote:
> Dear list members,
> I'd like to ask you for an explanation of the
> following strange error message that appeared
> using OWS.
> Observation:
> Old System : Informix SE 2.1 -> no error message
> New System : Informix OWS 7.1 -> error message!
> -------- STATEMENT ---------------------
> create table my_table
> (my_field1 char(1),
> my_field2 char(2));
> delete from my_table
> where my_field1 = "s"
> and my_field2 in ( select my_field2 from my_table where my_field1 = "t" )
> -- 360: Cannot modify table or view used in subquery.
Right, this was not restricted in the pre-4.0 products which were not
as SQL compliant and were produced before there was an SQL standard
anyway. You can do this like so:
select my_field2
from my_table
where my_field1 = "t"
into temp fred;
delete from my_table
where my_field1 = "s"
and my_field2 in ( select my_field2 from fred );
drop table fred;
Art S. Kagel