Insert from Select

[RPhillips@ce-a.com]

I'm trying to insert from a select statement but it is giving me an error
that I "Cannot modify table or view used in subquery." on my where clause.
Why?

INSERT INTO hospcontact
(hospital_id, name, sir_name, first_name, nick_name, use_nick_name, title,
street, street2,
city, state, zip, bl, bl_is_prim, stmt, stmt_is_prim, day, day_is_prim,
pcsr, pcsr_is_prim, special, special_is_prim,
ub92, ub92_is_prim, pl, pl_is_prim, agedbl, patient_acct_mgr,
last_modified, phone, fax, modified_by, email)

Select 500368, name, sir_name, first_name, nick_name, use_nick_name, title,
street, street2,
city, state, zip, bl, bl_is_prim, stmt, stmt_is_prim, day, day_is_prim,
pcsr, pcsr_is_prim, special, special_is_prim,
ub92, ub92_is_prim, pl, pl_is_prim, agedbl, patient_acct_mgr, last_modified,
phone, fax, modified_by, email
from hospcontact
where hospital_id=500349;
sending to informix-list

[John Carlson]

On Mon, 28 Jul 2003 16:26:48 -0400, "Art S. Kagel"
<kagel@bloomberg.net> wrote:

>On Mon, 28 Jul 2003 14:45:41 -0400, RPhillips wrote:
>
>Version and platform?? The problem was a race condition sort-of-thing.
>If you are inserting using a select from the same table you could get
>into an infinite loop internally where each new row becomes part of the
>select. I think this was solved in 9.30 and is now permitted??
>

No problems (apparently) in 9.30.

[Jonathan Leffler]

[email]RPhillips@ce-a.com[/email] wrote:

> I'm trying to insert from a select statement but it is giving me an error
> that I "Cannot modify table or view used in subquery." on my where clause.
> Why?

Because you have not upgraded to a sufficiently recent version of the
server. For a very long time, it was not possible to insert into
TableA if TableA was also mentioned in the SELECT statement.
Fortunately, that limitation has been removed in later versions.

> INSERT INTO hospcontact
> (hospital_id, name, sir_name, first_name, nick_name, use_nick_name, title,
> street, street2,
> city, state, zip, bl, bl_is_prim, stmt, stmt_is_prim, day, day_is_prim,
> pcsr, pcsr_is_prim, special, special_is_prim,
> ub92, ub92_is_prim, pl, pl_is_prim, agedbl, patient_acct_mgr,
> last_modified, phone, fax, modified_by, email)
>
> Select 500368, name, sir_name, first_name, nick_name, use_nick_name, title,
> street, street2,
> city, state, zip, bl, bl_is_prim, stmt, stmt_is_prim, day, day_is_prim,
> pcsr, pcsr_is_prim, special, special_is_prim,
> ub92, ub92_is_prim, pl, pl_is_prim, agedbl, patient_acct_mgr, last_modified,
> phone, fax, modified_by, email
> from hospcontact
> where hospital_id=500349;

--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

[Kristofer Andersson]

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<3F261374.9060502@earthlink.net>...

> [email]RPhillips@ce-a.com[/email] wrote:
>
> Because you have not upgraded to a sufficiently recent version of the
> server. For a very long time, it was not possible to insert into
> TableA if TableA was also mentioned in the SELECT statement.
> Fortunately, that limitation has been removed in later versions.
>

I have been getting error -360 on 9.3. In what version was it fixed?

[John Carlson]

On 29 Jul 2003 07:03:53 -0700, [email]anderssonk75@hotmail.com[/email] (Kristofer
Andersson) wrote:

>Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<3F261374.9060502@earthlink.net>...

>> [email]RPhillips@ce-a.com[/email] wrote:
>>
>> Because you have not upgraded to a sufficiently recent version of the
>> server. For a very long time, it was not possible to insert into
>> TableA if TableA was also mentioned in the SELECT statement.
>> Fortunately, that limitation has been removed in later versions.
>>

>
>I have been getting error -360 on 9.3. In what version was it fixed?

We're on 9.30.HC3 . . . no problems. Is this a table or a view??

[Kristofer Andersson]

John Carlson <john_carlson@whsmithusa.com> wrote in message news:<3e1divsi7b4vanoh7955bc7jcl4j72pmlv@4ax.com>. ..

>
> We're on 9.30.HC3 . . . no problems. Is this a table or a view??
>

Table. But looking back at it, it was not an insert but an update. So
maybe it has been fixed for insert but not for update...?

[Jean Sagi]

CREATE TEMP TABLE t(i INTEGER NOT NULL);
INSERT INTO t VALUES(10);
INSERT INTO t SELECT i + 10 FROM t;

-- Ok until here :

-- Trying to set all columns to 0
UPDATE t SET i = i - (SELECT i from t tx WHERE tx.i = t.i);

360: Cannot modify table or view used in subquery.

.... So ...

it works for INSERTs but not forUPDATEs...

that's some advance,


Jean Sagi.



Kristofer Andersson wrote:

> John Carlson <john_carlson@whsmithusa.com> wrote in message news:<3e1divsi7b4vanoh7955bc7jcl4j72pmlv@4ax.com>. ..
>

>>We're on 9.30.HC3 . . . no problems. Is this a table or a view??
>>

>
>
> Table. But looking back at it, it was not an insert but an update. So
> maybe it has been fixed for insert but not for update...?
>
>

--


Atte,


Jesús Antonio Santos Giraldo
[email]jeansagi@myrealbox.com[/email]
[email]jeansagi@netscape.net[/email]


sending to informix-list