update query

Dear All I want to update max 3 column in a table .to achieve this used following query but it is giving syntax error. *Query:*update care_encounter set create_id = "nierjesh" where encounter_nr in (select first 3 encounter_nr from care_encounter as enc order by enc.encounter_nr desc) ; Please guide me properly. Thanks in advance. Regards- Nierjesh --00163641722f5eafd6047428836f

Hi Nierjesh, Please try to include your platform information when posting to this site. By default, you cannot include the table that you are updating in a sub query clause. So the select first 3 encounter_nr from care_encounter as enc order byenc.encounter_nr desc clashes with the update care_encounter There are a couple of ways that you can overcome this problem. The first is to retrieve the values into a temp table..... select first 3 encounter_nr from care_encounter as enc order byenc.encounter_nr desc into temp t1 with no log; update care_encounter set create_id = "nierjesh" where encounter_nr in (select encounter_nr from t1); drop table t1; Depending on the version of Informix(I am using IDS 11.5) that you are using, you could also try update care_encounter set user_id = "nierjesh" where encounter_nr in ( select encounter_nr from table (multiset(select first 3 encounter_nr from care_encounter order by encounter_nr desc)) t1(encounter_nr) ) Hope this helps Mark > To: ids@iiug.org > From: nierjeshkumar@gmail.com > Subject: update query [17132] > Date: Tue, 22 Sep 2009 07:01:01 -0400 > > Dear All > > I want to update max 3 column in a table .to achieve this used following > query but it is giving syntax error. > > *Query:*update care_encounter set create_id = "nierjesh" where encounter_nr > in > (select first 3 encounter_nr from care_encounter as enc order by > enc.encounter_nr desc) ; > > Please guide me properly. > > Thanks in advance. > > Regards- > Nierjesh > > --00163641722f5eafd6047428836f > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ With Windows Live, you can organize, edit, and share your photos. http://www.microsoft.com/southafrica/windows/windowslive/products/photo-gallery- edit.aspx