Re: Updates or rows with multi column keys

I was able to take your example and the SQL that Keith and Serge provided and it worked, accross instances. If the remote table contains any OPAQUE datatypes, i.e. LVARCHAR, BOOLEAN, then that is why you are getting the "999: Not implemented yet" error. Point being, the solution provided you will work, if it is not then there is another issue that you are fighting now. ----- Original Message ---- From: Tim <mr.tim.dog@gmail.com> To: informix-list@iiug.org Sent: Tuesday, November 14, 2006 8:25:03 AM Subject: Re: Updates or rows with multi column keys 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 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

DL Redden wrote: > I was able to take your example and the SQL that Keith and Serge provided and it worked, accross instances. > > If the remote table contains any OPAQUE datatypes, i.e. LVARCHAR, BOOLEAN, then that is why you are getting the "999: Not implemented yet" error. Point being, the solution provided you will work, if it is not then there is another issue that you are fighting now. Ok sorry, bit of a cut'n'past problem, ableit with a bit of a misleading Informix error. I'd cut'n'paste the subselect and forgot to put the table to be updated into the from clause, this seems to cause the misleading error. I also succeeded with the cross DB query, but it was extreamly slow as it seems to be running the sub queries three times, one for each subselect. I've done this once now into a an indexed temp table with more efficient success. No time to play around with better ways unfortunately!! Thanks people. Tim > ----- Original Message ---- > From: Tim <mr.tim.dog@gmail.com> > To: informix-list@iiug.org > Sent: Tuesday, November 14, 2006 8:25:03 AM > Subject: Re: Updates or rows with multi column keys > > > 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 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

Tim wrote: > DL Redden wrote: > >>I was able to take your example and the SQL that Keith and Serge provided and it worked, accross instances. >> >>If the remote table contains any OPAQUE datatypes, i.e. LVARCHAR, BOOLEAN, then that is why you are getting the "999: Not implemented yet" error. Point being, the solution provided you will work, if it is not then there is another issue that you are fighting now. > > > Ok sorry, bit of a cut'n'past problem, ableit with a bit of a > misleading Informix error. I'd cut'n'paste the subselect and forgot to > put the table to be updated into the from clause, this seems to cause > the misleading error. > > I also succeeded with the cross DB query, but it was extreamly slow as > it seems to be running the sub queries three times, one for each > subselect. I've done this once now into a an indexed temp table with > more efficient success. > > No time to play around with better ways unfortunately!! <SNIP> You could probably use Jonathan Leffler's sqlupload utility from the sqlcmd package could probably do this efficiently including inserting records that are not already there. Art S. Kagel