Re: Joined Row Update
Jose da Fonseca wrote: > Can anyone tell me if it is possible to do a joined row update (available in XPS) on ids 10 ?? > > syntax on XPS > > Update table1 > set table1.col1 = table2.col3 > from table1, table2 > where table1.col1 = table2.col1; Not supported in IDS. You have to use sub-queries. There have been at least two other questions on this in the last month. Peruse the archive. Art S. Kagel > Thank you in advance > Jos' >
Roadmap I posted here a few weeks ago says it hopefully will be in
vNext (10.5) from memory.
If you can't wait that long, an alternative is a Foreach loop in a
stored procedure. I find this quite a bit quicker, depends on how big
the cost of a sub-select was in the first place.
-- lock table in exclusive mode is done so as not to run out of locks
-- the database or have it spend ages dynamically allocating them.
-- pdq helps with multiple temps / frag tables.
set pdqpriority 2;
CREATE PROCEDURE pptest();
define lv_short_desc char (10);
define lv_long_desc char (40);
define lv_pri_key char (12);
begin work;
lock table real_table in exclusive mode;
foreach c_read_old for
select pkey, new_s_desc, new_l_desc
into lv_pri_key, lv_short_desc, lv_long_desc
from upd_tab
update real_table
set short_desc = lv_short_desc,
long_desc = lv_long_desc
where pkey = lv_pri_key;
end foreach;
commit work;
END PROCEDURE;
create temp table real_table
(
pkey char (12),
short_desc char (10),
long_desc char (60)
) with no log;
-- might also like to load from a file here of records you want to
update.
create temp table upd_tab
(
pkey char (12),
new_s_desc char (10),
new_l_desc char (60)
) with no log;
insert into real_table values ("NSW01A100001", "snake", "Eastern BrownSnake - try not get bitten");
insert into real_table values ("NSW01A100002", "csh", "Cone Shell -annoying if darted");
insert into real_table values ("NSW01A100003", "gwh", "Great White -friendly unless blood about");
insert into real_table values ("NSW01A100004", "tgsh", "Tiger Shark -kills things for fun");
insert into real_table values ("NSW01A100005", "roo", "Kangeroo - pest,people shoot for fun");
insert into upd_tab values ("NSW01A100005", "roo", "Kangeroo - lovely
Creature, don't kill");
insert into upd_tab values ("NSW01A100003", "koa", "Koala - Worldslaziest animal");
select * from real_table;
execute procedure pptest();
select * from real_table;
drop table real_table;
drop table upd_tab;
drop procedure pptest;
Let us know if that helps.
Art S. Kagel wrote:
> Jose da Fonseca wrote:
> > Can anyone tell me if it is possible to do a joined row update (available in XPS) on ids 10 ??
> >
> > syntax on XPS
> >
> > Update table1
> > set table1.col1 = table2.col3
> > from table1, table2
> > where table1.col1 = table2.col1;
>
> Not supported in IDS. You have to use sub-queries. There have been at
> least two other questions on this in the last month. Peruse the archive.
>
> Art S. Kagel
>
> > Thank you in advance
> > José
> >