Re: -360 Cannot modify table or view used in subquery
Hi,
Pay attention,
When you want update a table using a subquery, you cannot use the
table that you update to 'select' a resultset of rows to decide which
rows you update.
I mean:
If you update a column in the table, and the 'resultset' of rows
selected in the subquery can change because that you could be in a
endless loop.
When I need do it , I use to create a temp table with the lines
pre-selected in the subquery and after that I use that temp table in
the subquery.
PS: The subquery means it is in DB2 database.... not in Informix.
For instance:
I would do:
SELECT col1 FROM table1 , table2
where table1.col2 = table2.col2
and table1.col3 = "value"
and table2.col4 = "value"
INTO TEMP TABLE tmp_col1 WITH NO LOG ;
UPDATE table1 SET col5 = "constant"
WHERE col1 in ( SELECT t.col1 from tmp_col1 t );
I'm sure it works well !!
Good luck !!!
[] from Brazil. Roberto F.
"LUQUE Valdivia Omar" <luque001@marina.mil.pe> wrote in message news:<brng2h$m3p$1@terabinaries.xmission.com>...
> Hello everybody,
>
> My query :
>
> UPDATE GN_PACIENTE
> SET GRUPO = "G00"
> WHERE NROHIS IN
> (SELECT NROHIS FROM GN_PACIENTE INNER JOIN TBPERPRIMARI ON TBPERPRIMARI.PRICDCIP || "00" = GN_PACIENTE.NROHIS WHERE pricdsituacion = 1 AND priidtippersona < 6 )
>
> I read the Informix error messages and i saw this paragraph, but I don't Understand that : "This action is not allowed because of the danger of getting into an endless loop."
> Why is danger?
>
> I can do that in SQLServer and Access, why not in Informix?
>
> Thanks
>
> Omar.
>
> sending to informix-list