Insert from Select

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

On Mon, 28 Jul 2003 14:45:41 -0400, RPhillips@ce-a.com 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? > >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 The basic query structure seems to work in 9.30. Is hospcontact a view??

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?? Art S. Kagel > 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