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 theposting 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!"