vj wrote:
How to Update multiple tables in a single SQL update Statement?
Is there any way out?
vj.
with u1 as (select * from new table(update t1 include (pk2 int, c2 int)
set (c1, pk2, c2)
= (select c1, pk2, c2 from s where t1.pk = s.pk1)
where exists (select 1 from s where t1.pk = s.pk1))),
u2 as (select * from new table(update t2
set c2 = (select c2 from u1 where t2.pk = u1.pk2)
where exists (select 1 from u1 where t2.pk = u1.pk2)))
select count(*) from u2;
Would be nicer with select from merge... one of these days... *sigh*
Of course you can also use
--#SET TERMINTAOR %
BEGIN ATOMIC
FOR x AS SELECT * FROM S DO
UPDATE t1 SET c1 = x.c1 ... ;
UPDATE t2 SET c2 = x.c2 ... ;
END FOR;
END%
(one statement - check the explain :-)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
