Error 360 when I use a subquery
Hello,
the 2 tables fyar1sta and fyar2sta are 1:1 related with the field artikel
I must copy bewprs to aktvkp in the table fyar2sta but only for those
record which
have prodchat equal B in table fyar1sta
I tryed this with the following query, but I get the error message
360: Cannot modify table or view used in subquery.
Error in line 5Near character position 27
UPDATE fyar2sta
SET aktvkp = bewprs
WHERE artikel IN (
SELECT a1.artikel
FROM fyar1sta a1, fyar2sta a2
WHERE a1.artikel = a2.artikel
AND a1.prodchar = B);
Where is my mistake
Thanks
Am 28.02.2012 09:36, schrieb Ralf Hackmann:
> Hello,
>
> the 2 tables fyar1sta and fyar2sta are 1:1 related with the field artikel
>
> I must copy bewprs to aktvkp in the table fyar2sta but only for those
> record which
> have prodchat equal B in table fyar1sta
>
> I tryed this with the following query, but I get the error message
>
> 360: Cannot modify table or view used in subquery.
> Error in line 5> Near character position 27
>
> UPDATE fyar2sta
> SET aktvkp = bewprs
> WHERE artikel IN (
> SELECT a1.artikel
> FROM fyar1sta a1, fyar2sta a2
> WHERE a1.artikel = a2.artikel
> AND a1.prodchar = B);>
> Where is my mistake
The long text of the message reads:
"The UPDATE, INSERT, or DELETE statement uses data taken from the same
table in a subquery. This action is not allowed because of the danger of
getting into an endless loop. Select the input data into a temporary
table first, and then refer to the temporary table in the UPDATE or
INSERT statement."
It both tells the reason (fyar2sta in update statement and in subquery)
and the solution (use two steps).
HTH
Christian
Christian's solution of sending the subquery's output to a temp table then
using the temp table in the update instead is good. The alternative, which
will likely perform slower would be to make the subquery correlated to the
rows in the update:
UPDATE fyar2sta
SET aktvkp = bewprs
WHERE artikel = (
SELECT a1.artikel
FROM fyar1sta a1
WHERE a1.artikel = fyar2sta.artikel
AND a1.prodchar = B
);
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Feb 28, 2012 at 3:36 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote:
> Hello,
>
> the 2 tables fyar1sta and fyar2sta are 1:1 related with the field artikel
>
> I must copy bewprs to aktvkp in the table fyar2sta but only for those
> record which
> have prodchat equal B in table fyar1sta
>
> I tryed this with the following query, but I get the error message
>
> 360: Cannot modify table or view used in subquery.
> Error in line 5> Near character position 27
>
> UPDATE fyar2sta
> SET aktvkp = bewprs
> WHERE artikel IN (
> SELECT a1.artikel
> FROM fyar1sta a1, fyar2sta a2
> WHERE a1.artikel = a2.artikel
> AND a1.prodchar = B);>
> Where is my mistake
>
> Thanks
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
If I start this query, the SE runs in an endless loop.
I am not shore how to code Cristian´s solution with the temp table,
because I am not an SQL expert.
Can anyone help me?
Ralf
Am Dienstag, 28. Februar 2012 11:54:49 UTC+1 schrieb Art S. Kagel:
>
> UPDATE fyar2sta
>
> SET aktvkp = bewprs
>
> WHERE artikel = (
>
> SELECT a1.artikel>
> FROM fyar1sta a1
>
> WHERE a1.artikel = fyar2sta.artikel
> AND a1.prodchar = B
> );