Re: Why does ISQL give me a syntax error?

As someone said this for some reason needs double paranteses to work: UPDATE <table> SET (fld1,fld2) = ((SELECT fld1,fld2 FROM <tbl2> ....)) But you have another problem with the orignial example update statement: UPDATE porder SET (po_recdt, po_shipdt) = ((SELECT tpo_recdt, tpo_shipdt FROM potmp WHERE tpo_c_no = po_c_no AND tpo_num = po_num)) WHERE po_c_no IN (SELECT tpo_c_no FROM potmp) AND po_num IN (SELECT tpo_num FROM potmp); On principle it won't work, although it might in your case. If po_num is unique over all po_c_no you won't need both IN clauses, so I assume this is not the case. Her's some example data: Table potmp: tpo_c_no tpo_num 1 1 1 2 2 1 2 2 2 3 Table porder: po_c_no po_num 1 1 1 2 1 3 2 1 2 2 3 1 3 2 The where clause of the update statement: WHERE po_c_no IN (SELECT tpo_c_no FROM potmp) AND po_num IN (SELECT tpo_num FROM potmp); translates into this: WHERE po_c_no IN (1,2) AND po_num IN (1,2,3) Which means it will try to update these columns of porder: (1,1), (1,2), (1,3), (2,1), (2,2) The problem is the third one containing 1,3. The where clause of the select statement used in the set part of the update statement: WHERE tpo_c_no = po_c_no AND tpo_num = po_num won't find any corresponding rows in the potmp-table. On my engine (OnLine 7.1 on SCO Unix) this results in po_recdt and po_shipdt beeing set to NULL where po_c_no = 1 and po_num = 3 This is probably not what you want! This is not an Informix problem of course. It is SQL! Genearly you can't use multiple "IN" clauses in the same where clause. You need a single field that is unique over all rows that are selected by the where clause excluding the "IN" part. That field can then be used in the "IN" part. (The real solution would have been something like: WHERE (po_c_no, po_num) IN (SELECT tpo_c_no, tpo_num FROM potmp) but that isn't part of SQL although it probably should have been.) Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company > barrye@gol.com (Barry Ewards) writes: > I have just encountered a similar problem. > I seems as though Informix has a problem with UPDATE statements like > > UPDATE <table> SET (fld1,fld2) = (SELECT fld1,fld2 FROM <tbl2> ....) > > It only seems to support > > UPDATE <table> SET fld1 = (SELECT fld1 FROM ....... ) > > This is contrary to what is in the manuals. I am running Informix 5.0 > > Barry Edwards > barrye@gol.com > > >@ wrote: > > > >UPDATE porder > > SET (po_recdt, po_shipdt) = (SELECT tpo_recdt, tpo_shipdt > > FROM potmp > > WHERE tpo_c_no = po_c_no AND tpo_num = po_num) > > WHERE po_c_no IN (SELECT tpo_c_no FROM potmp) > > AND po_num IN (SELECT tpo_num FROM potmp); > > > >Greg Williams > >gregw@gtalumni.org > > > > >>>>