Re: Why does ISQL give me a syntax error?

Barry Ewards (barrye@gol.com) wrote: : I have just encountered a similar problem. My work-around was to do : something like this:- .... : I seems as though Informix has a problem with UPDATE statements like : UPDATE <table> SET (fld1,fld2) = (SELECT fld1,fld2 FROM <tbl2> ....) The correct syntax is: UPDATE <table> SET (fld1,fld2) = ((SELECT fld1,fld2 FROM <tbl2> ....)) ^^ ^^ Note the double parentheses. The outer set of parentheses delimit the value list to be used to update each column in the column list. The inner parentheses delimit the expression to be used to determine the values for one or more of those columns. In your illustrated example, the expression (SELECT...) happens to determine the values for _all_ the columns in the column list. But consider this example (I exaggerate the format for illustrative purposes): UPDATE <table> SET (fld1,fld2,fld3) = ( (SELECT fld1,fld2 FROM <tbl2> .... ), (SELECT fld3 FROM <tbl3> ) ); or this one: UPDATE <table> SET (fld1,fld2,fld3) = ( (SELECT fld1 FROM <tbl2> .... ), CURRENT, (SELECT fld3 FROM <tbl3> ) ); : This is contrary to what is in the manuals. Take a closer look. . ....... ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.