need help for a update command

Hello , i want to do the following, maybe some of you can help :-) 2 indentical tables : nr, index , w1,w2,w3 Problem: How can i update table T1 with the values w1,w2,w3 of table T2 , if the index of the tables is nr,index. i tried something like "update T1 a set (w1,w2,w3)= (select b.w1, b.w2, b.w3 from T2 b where a.nr=b.nr and a.index=b.index) But this doesnt work :-( Can you help me :-))))) Thorsten

Thorsten Geelhaar wrote: > i want to do the following, maybe some of you can help :-) > > 2 indentical tables : nr, index , w1,w2,w3 > > Problem: How can i update table T1 with the values w1,w2,w3 of table T2 > , if the > index of the tables is nr,index. > > i tried something like "update T1 a set (w1,w2,w3)= (select b.w1, b.w2, > b.w3 from T2 b where > a.nr=b.nr and a.index=b.index) > > But this doesnt work :-( It doesn't work because it nulls all the rows in T1 where there isn't a matching row in T2? Oh - no, syntax error (you can't alias the table you're updating) -- but then you'd have run into the other problem too. UPDATE T1 SET (w1, w2, w3) = (SELECT T2.w1, T2.w2, T2.w3 FROM T2 WHERE T1.nr = T2.nr AND T1.index = T2.index) WHERE EXISTS(SELECT * FROM T2 WHERE T1.nr = T2.nr AND T1.index = T2.index); I haven't verified this formulation - you should test it. The key points are (1) you can't alias the updated table name in the UPDATE statement, and (2) you have to ensure that only the rows in T2 with a corresponding row mentioned in T2 are altered. I think the correlated EXISTS sub-query fixes that. With a single-column key (eg T1.nr), I'd have used "WHERE nr IN (SELECT T2.nr FROM T2)" in the WHERE clause of the UPDATE statement - as opposed to in the sub-select statements. The performance of such queries won't be outstanding because of the correlated sub-queries, but it should be quicker than doing it by fetching data from T2 to the client and then sending back the updates to T1 to the server. (If it isn't quicker, the optimizer has blown it, severely!) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Hi, you can try this: begin; update t1 set ( w1, w2, w3 ) = ( (select w1 from t2 where t2.nr=t1.nr and t2.index=t1.index), (select w2 from t2 where t2.nr=t1.nr and t2.index=t1.index), (select w3 from t2 where t2.nr=t1.nr and t2.index=t1.index)) where exists ( select * from t2 where t2.nr=t1.nr and t2.index=t1.index); It works, if you have unique ( nr, index) values in t2 table. Sanja Thorsten Geelhaar <thorsten.geelhaar@materna.de> wrote in message news:<3EC1F95C.5080802@materna.de>... > Hello , > > i want to do the following, maybe some of you can help :-) > > 2 indentical tables : nr, index , w1,w2,w3 > > Problem: How can i update table T1 with the values w1,w2,w3 of table T2 , if the > index of the tables is nr,index. > > i tried something like "update T1 a set (w1,w2,w3)= (select b.w1, b.w2, b.w3 from T2 b where > a.nr=b.nr and a.index=b.index) > > But this doesnt work :-( > > Can you help me :-))))) > > Thorsten

Thorsten Geelhaar <thorsten.geelhaar@materna.de> wrote in message news:<3EC1F95C.5080802@materna.de>... > > i tried something like "update T1 a set (w1,w2,w3)= (select b.w1, b.w2, b.w3 from T2 b where > a.nr=b.nr and a.index=b.index) > > But this doesnt work :-( try this (note the double brackets): update T1 set (w1,w2,w3)= ((select b.w1, b.w2, b.w3 from T2 b where T1.nr=b.nr and T1.index=b.index))