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

LUQUE Valdivia Omar wrote:

> 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?

What version of Informix?

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

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

In my last message, I wrote:
"The subquery means be a DB2 database",
I did want mean that the sintax in the subquery that you wrote:
"(SELECT NROHIS FROM GN_PACIENTE INNER JOIN TBPERPRIMARI ON
TBPERPRIMARI.PRICDCIP || "00" = GN_PACIENTE.NROHIS WHERE
pricdsituacion = 1 AND priidtippersona < 6 )"
Seems be to db2 or sqlserver sintax ..... not in Informix SQL sintax.

Best regards.

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