Updates or rows with multi column keys

Hi, I've a bit of an SQL question (for use on IDS10): I want to do an update of two columns in each row, indexed by another two columns of that row from another table. For example: create table src ( key1 integer, key2 integer, val1 integer, val2 integer, primary key (key1, key2) ); create table dst ( key1 integer, key2 integer, val1 integer, val2 integer, primary key (key1, key2) ); insert into src values (1, 1, 1, 1); insert into src values (2, 2, 1, 1); insert into dst values (1, 1, 0, 0); insert into dst values (2, 2, 0, 0); update dst set dst.val1 = src.val1, dst.val2 = src.val2 from src -- Can't use from here! :( where dst.key1 = src.key1 and dst.key2 = src.key2; The problem is, I can't use the 'from' here. In real life these tables are on different servers as we're doing a kind of a migration thing. I've been looking through the manuals and all sorts and even playing around with row costructors which I thought may have helped, but no luck! Any ideas on how I can go about this, or something along the same lines of? Cheers, Tim

On 14 Nov 2006 05:25:35 -0800, Tim <mr.tim.dog@gmail.com> wrote: > Hi, > > I've a bit of an SQL question (for use on IDS10): > > I want to do an update of two columns in each row, indexed by another > two columns of that row from another table. For example: > > create table src > ( > key1 integer, > key2 integer, > val1 integer, > val2 integer, > primary key (key1, key2) > );> > create table dst > ( > key1 integer, > key2 integer, > val1 integer, > val2 integer, > primary key (key1, key2) > );> > insert into src values (1, 1, 1, 1); > insert into src values (2, 2, 1, 1); > insert into dst values (1, 1, 0, 0); > insert into dst values (2, 2, 0, 0);> > update dst > set dst.val1 = src.val1, > dst.val2 = src.val2 > from src -- Can't use from here! :( > where dst.key1 = src.key1 and > dst.key2 = src.key2; > > The problem is, I can't use the 'from' here. In real life these tables > are on different servers as we're doing a kind of a migration thing. > > I've been looking through the manuals and all sorts and even playing > around with row costructors which I thought may have helped, but no > luck! > > Any ideas on how I can go about this, or something along the same lines > of? > > Cheers, > > Tim > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Tim UPDATE dst SET val1 = ( SELECT val1 FROM src WHERE key1 = dst.key1 AND key2 = dst.key2 ) , val2 = ( SELECT val2 FROM src WHERE key1 = dst.key1 AND key2 = dst.key2 ) ; Keith

Keith Simmons wrote: > On 14 Nov 2006 05:25:35 -0800, Tim <mr.tim.dog@gmail.com> wrote: >> Hi, >> >> I've a bit of an SQL question (for use on IDS10): >> >> I want to do an update of two columns in each row, indexed by another >> two columns of that row from another table. For example: >> >> create table src >> ( >> key1 integer, >> key2 integer, >> val1 integer, >> val2 integer, >> primary key (key1, key2) >> );>> >> create table dst >> ( >> key1 integer, >> key2 integer, >> val1 integer, >> val2 integer, >> primary key (key1, key2) >> );>> >> insert into src values (1, 1, 1, 1); >> insert into src values (2, 2, 1, 1); >> insert into dst values (1, 1, 0, 0); >> insert into dst values (2, 2, 0, 0);>> >> update dst >> set dst.val1 = src.val1, >> dst.val2 = src.val2 >> from src -- Can't use from here! :( >> where dst.key1 = src.key1 and >> dst.key2 = src.key2; >> >> The problem is, I can't use the 'from' here. In real life these tables >> are on different servers as we're doing a kind of a migration thing. >> >> I've been looking through the manuals and all sorts and even playing >> around with row costructors which I thought may have helped, but no >> luck! >> >> Any ideas on how I can go about this, or something along the same lines >> of? >> >> Cheers, >> >> Tim >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > Tim > > UPDATE dst > SET val1 = ( SELECT val1 FROM src > WHERE key1 = dst.key1 > AND key2 = dst.key2 ) > , val2 = ( SELECT val2 FROM src > WHERE key1 = dst.key1 > AND key2 = dst.key2 ) WHERE EXISTS(SELECT 1 FROM src WHERE key1 = dst.key1 AND key2 = dst.key2) > ; Otherwise you'll get a lot of NULLs for the not matched columns. Does IDS10 support row-assignment? Would be more efficient (at least to type): SET (val1, val2) = (SELECT val1, val2 FROM src ...) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

Serge Rielau wrote: > Keith Simmons wrote: > > On 14 Nov 2006 05:25:35 -0800, Tim <mr.tim.dog@gmail.com> wrote: > >> Hi, > >> > >> I've a bit of an SQL question (for use on IDS10): > >> > >> I want to do an update of two columns in each row, indexed by another > >> two columns of that row from another table. For example: > >> > >> create table src > >> ( > >> key1 integer, > >> key2 integer, > >> val1 integer, > >> val2 integer, > >> primary key (key1, key2) > >> );> >> > >> create table dst > >> ( > >> key1 integer, > >> key2 integer, > >> val1 integer, > >> val2 integer, > >> primary key (key1, key2) > >> );> >> > >> insert into src values (1, 1, 1, 1); > >> insert into src values (2, 2, 1, 1); > >> insert into dst values (1, 1, 0, 0); > >> insert into dst values (2, 2, 0, 0);> >> > >> update dst > >> set dst.val1 = src.val1, > >> dst.val2 = src.val2 > >> from src -- Can't use from here! :( > >> where dst.key1 = src.key1 and > >> dst.key2 = src.key2; > >> > >> The problem is, I can't use the 'from' here. In real life these tables > >> are on different servers as we're doing a kind of a migration thing. > >> > >> I've been looking through the manuals and all sorts and even playing > >> around with row costructors which I thought may have helped, but no > >> luck! > >> > >> Any ideas on how I can go about this, or something along the same lines > >> of? > >> > >> Cheers, > >> > >> Tim > >> > >> _______________________________________________ > >> Informix-list mailing list > >> Informix-list@iiug.org > >> http://www.iiug.org/mailman/listinfo/informix-list > >> > > Tim > > > > UPDATE dst > > SET val1 = ( SELECT val1 FROM src > > WHERE key1 = dst.key1 > > AND key2 = dst.key2 ) > > , val2 = ( SELECT val2 FROM src > > WHERE key1 = dst.key1 > > AND key2 = dst.key2 ) > WHERE EXISTS(SELECT 1 FROM src > WHERE key1 = dst.key1 > AND key2 = dst.key2) > > ; > Otherwise you'll get a lot of NULLs for the not matched columns. > > Does IDS10 support row-assignment? Would be more efficient (at least to > type): > SET (val1, val2) = (SELECT val1, val2 FROM src ...) > > Cheers > Serge Thanks Serge. Unfortunately it doesn't :( I've also just tried Keiths suggestion with fully qualified tables between DBs and the response I get is: 999: Not implemented yet. Doh... bugger!! Ok, looks like a temp table and a bit of selective iteration. Cheers, Tim

Serge Rielau wrote: > Keith Simmons wrote: > > On 14 Nov 2006 05:25:35 -0800, Tim <mr.tim.dog@gmail.com> wrote: > >> Hi, > >> > >> I've a bit of an SQL question (for use on IDS10): > >> > >> I want to do an update of two columns in each row, indexed by another > >> two columns of that row from another table. For example: > >> > >> create table src > >> ( > >> key1 integer, > >> key2 integer, > >> val1 integer, > >> val2 integer, > >> primary key (key1, key2) > >> );> >> > >> create table dst > >> ( > >> key1 integer, > >> key2 integer, > >> val1 integer, > >> val2 integer, > >> primary key (key1, key2) > >> );> >> > >> insert into src values (1, 1, 1, 1); > >> insert into src values (2, 2, 1, 1); > >> insert into dst values (1, 1, 0, 0); > >> insert into dst values (2, 2, 0, 0);> >> > >> update dst > >> set dst.val1 = src.val1, > >> dst.val2 = src.val2 > >> from src -- Can't use from here! :( > >> where dst.key1 = src.key1 and > >> dst.key2 = src.key2; > >> > >> The problem is, I can't use the 'from' here. In real life these tables > >> are on different servers as we're doing a kind of a migration thing. > >> > >> I've been looking through the manuals and all sorts and even playing > >> around with row costructors which I thought may have helped, but no > >> luck! > >> > >> Any ideas on how I can go about this, or something along the same lines > >> of? > >> > >> Cheers, > >> > >> Tim > >> > >> _______________________________________________ > >> Informix-list mailing list > >> Informix-list@iiug.org > >> http://www.iiug.org/mailman/listinfo/informix-list > >> > > Tim > > > > UPDATE dst > > SET val1 = ( SELECT val1 FROM src > > WHERE key1 = dst.key1 > > AND key2 = dst.key2 ) > > , val2 = ( SELECT val2 FROM src > > WHERE key1 = dst.key1 > > AND key2 = dst.key2 ) > WHERE EXISTS(SELECT 1 FROM src > WHERE key1 = dst.key1 > AND key2 = dst.key2) > > ; > Otherwise you'll get a lot of NULLs for the not matched columns. > > Does IDS10 support row-assignment? Would be more efficient (at least to > type): > SET (val1, val2) = (SELECT val1, val2 FROM src ...) You need to use double ()s ... update dst set (val1, val2) = (( select val1, val2 from src where dst.key1 = src.key1 and dst.key2 = src.key2 )) where exists (select 0 from src where dst.key1 = src.key1 and dst.key2 = src.key2 ); -- rh

richard.harnden@googlemail.com wrote: > You need to use double ()s ... > > update dst > set (val1, val2) > = (( > select val1, val2 > from src > where dst.key1 = src.key1 > and dst.key2 = src.key2 > ))Cool! -- Serge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html