Re: Update with select

"Manuel A. Daponte Santiago" <mdaponte@prtc.net> wrote: >If I try to execute an update of one or more columns with the results of a >select I always get a syntax error !!! > >update cust_shp > set (ship_name, address_1) = > (select tabname, tabname from systables where tabid = 1) ># ^ ># 201: A syntax error has occurred. ># > where slmn_num[1,1]="2" Updates of multiple columns require 2 sets of parentheses around the select. I don't know why, but that's how the manual describes the syntax. update cust_shp set (ship_name, address_1) = ((select tabname, tabname from systables where tabid = 1)) where slmn_num[1,1]="2" >Also with constants: >update cust_shp > set (ship_name, address_1) = > (select 'a', 'b' from systables where tabid = 1) ># ^ ># 201: A syntax error has occurred. ># Same as first case. Use double parentheses >Or with only one column: >update cust_shp > set (ship_name) = > (select 'a' from systables where tabid = 1) ># ^ ># 201: A syntax error has occurred. ># Not sure about this, but I think the parentheses around the column to be updated imply a multi-column style update syntax. Either drop the first set of parentheses (as below) or put another set around the select. >But this works !!! So, I can't update several columns in one update !!! >update cust_shp > set ship_name = > (select 'a' from systables where tabid = 1) > >I have: > Informix Dynamic Server Version 7.31.UC2 > DB-Access Version 7.31.UC2 > SCO Release = 3.2v5.0.4 > >Does somebody else have a similar problem? > >-- >Manuel A. Daponte Santiago >Systems Consultant, ICP > > >

Jeff Larsen wrote: > "Manuel A. Daponte Santiago" <mdaponte@prtc.net> wrote: > > >If I try to execute an update of one or more columns with the results of a > >select I always get a syntax error !!! > > > >update cust_shp > > set (ship_name, address_1) = > > (select tabname, tabname from systables where tabid = 1) > ># ^ > ># 201: A syntax error has occurred. > ># > > where slmn_num[1,1]="2" > > Updates of multiple columns require 2 sets of parentheses > around the select. I don't know why, but that's how the > manual describes the syntax. > > update cust_shp > set (ship_name, address_1) = > ((select tabname, tabname from systables where tabid = 1)) > where slmn_num[1,1]="2" That's right. The outer set of parentheses encloses the list of values, like the parentheses on the LHS enclose the list of columns. Then the sub-select needs its own surrounding set of parentheses. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>