Re: Why does ISQL give me a syntax error?

I have just encountered a similar problem. My work-around was to do something like this:- UPDATE porder SET (po_recd = (SELECT tpo_recdt 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); UPDATE porder SET (po_shiptd = (SELECTpo_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); I think that you can get the idea .... 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: >I have the permanent table porder and a temporary table potmp. I have data >in potmp that I want to update against data in porder using. I'm trying to use >the SQL statement below, but I get a syntax error on the first SELECT value >as part of the SET statment. This is almost exactly what is in the manual for >how to update one table against another. I do know that sometimes the data >in the SELECT from potmp can be null. So, why do I get a syntax error from isql >when I try to run this line? What stupid thing am I missing? :-) >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