Re: brain dead sql syntax help update stmt

Jonathan, thanks for trying. Sorry I forgot to submit some useful info: ver 7.31 using dbaccess or sqleditor. I get err -201, which points to some obscure syntax problem. I assumed someone would gleefully point out a bad paranthesis or comma or someother simple mistake. Doug ----- Original Message ----- From: "Jonathan Leffler" <jleffler@informix.com> To: <informix-list@iiug.org> Sent: Thursday, January 25, 2001 5:07 PM Subject: Re: brain dead sql syntax help update stmt > Doug Fossmeyer wrote: > > > > Ok, I am brain dead and this does not work: > > > > update t1 set (col1, col2, col3, col4) = ((select col1, col2, col3, col4 > > from t2 where t1.id = t2.id and t2.col5 = "PM"));> > > > All I get is a "syntax error" and I need someone's help. Actual solution is > > wonderful and the explanation is bonus points. > > Using Foundation 2000 9.21.UC1 and SQLCMD, I typed in the following > CREATE TABLE statements and pasted your UPDATE statement from the> posting with the results shown: > > $ sqlcmd -d stores > SQL[2013]: create table t1 (id serial, col1 char(2), col2 char(2), > > col3 char(2), col4 char(2)); > SQL[2014]: create table t2 (id serial, col1 char(2), col2 char(2), > > col3 char(2), col4 char(2), col5 char(2)); > SQL[2015]: update t1 set (col1, col2, col3, col4) = ((select col1, col2, > col3, col4 > > from t2 where t1.id = t2.id and t2.col5 = "PM")); > SQL[2016]: > $> > So, there is nothing intrinsically wrong with the syntax. Question: > what language are you using? If it is ESQL/C, could your statement be > longer than the buffer, so the server is not seeing the whole UPDATE > statement? > > What database server are you using? On which platform? > > And beware the rows in t1 which do not have a matching row in t2; you > normally need a WHERE clause on the UPDATE (as opposed to the SELECT > sub-query) to only update those rows in t1 which have a corresponding > row in t2 -- otherwise you get a lot of nulls in your database. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!" >

>>>>> "Doug" == Doug Fossmeyer <dfossmeyer@gmx.net> writes: Doug> Jonathan, thanks for trying. Doug> Sorry I forgot to submit some useful info: ver 7.31 using Doug> dbaccess or sqleditor. This kind of SQL will work in IDS.2000 but not against 7.3x engines (I tried this kind of UPDATE a few weeks ago). Its a new feature/functionality of the IDS.2000 package. I'll try to find the documentation (if dim memory serves, it was the SQL syntax book for UPDATE). Mark -- "Laughter is the closest distance between two people." -- Victor Borge