Update query with table reference in subquery

Hi All, I have update query which uses the base table in subquery, it gives error: "360: Cannot modify table or view used in subquery" UPDATE PSCIREF_tmp SET REF_CINAME = 'x' WHERE REF_CINAME = 'y' AND BCNAME IN (SELECT DISTINCT BCNAME FROM PSCIREF_tmp REF WHERE REF.REF_CINAME = PSCIREF_tmp.REF_CINAME) Anyidea, how I can rewrite the query to execute it in Informix. Thanks,

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.

Just run it without the subquery. Your update says: Update the ref_ciname column to 'x' where it is 'y' for all rows that have ref_ciname equal to the same value for ref_ciname for each value of bcname. The simple update has the exact same result: UPDATE PSCIREF_tmp SET REF_CINAME = 'x' WHERE REF_CINAME = 'y'; If I'm wrong, your only recourse is to copy the ref_ciname and bcname columns into another temp table and filter using that table in the sub-query. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Dec 8, 2009 at 1:16 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > Hi All, > I have update query which uses the base table in subquery, it gives error: > > "360: Cannot modify table or view used in subquery" > > UPDATE PSCIREF_tmp > SET REF_CINAME = 'x' > WHERE REF_CINAME = 'y' > > AND BCNAME IN (SELECT DISTINCT BCNAME FROM PSCIREF_tmp REF > WHERE REF.REF_CINAME = PSCIREF_tmp.REF_CINAME) > > Anyidea, how I can rewrite the query to execute it in Informix. > > Thanks, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd644fbcd20047a35cf2b

It helps to have which version you are on. In version 11.50.xC2 the use of the same table in a subquery of a delete or update was greatly improved. Please see the release notes of 11.50.xC2 for the exact details. The the section title in the release notes is called "Subquery support in update and delete statement" John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "PRADEEP KUMAR" <pkyadav1@hotmail.com> = = To: ids@iiug.org = = Date: 12/07/2009 10:16 PM = = Subject: Update query with table reference in subquery [18294] = = Sent by: ids-bounces@iiug.org = = Hi All, I have update query which uses the base table in subquery, it gives err= or: "360: Cannot modify table or view used in subquery" UPDATE PSCIREF_tmp SET REF_CINAME =3D 'x' WHERE REF_CINAME =3D 'y' AND BCNAME IN (SELECT DISTINCT BCNAME FROM PSCIREF_tmp REF WHERE REF.REF_CINAME =3D PSCIREF_tmp.REF_CINAME) Anyidea, how I can rewrite the query to execute it in Informix. Thanks, ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =