By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

How to Update multiple tables in a single SQL update Statement?

P: n/a
vj
How to Update multiple tables in a single SQL update Statement?

Is there any way out?

vj.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.