Update across 2 tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arun Srinivasan - 09 Jan 2008 21:59 GMT

I have 2 tables.
test1 : A integer, B integer - (1,1) , (2,2)
test2 : A integer, B integer (1,2) , (2,3)

I have to update test1, set test1.b = test2. b where test1.a =
test2.a

Can this be done ?

Reply to this Message

Lennart - 10 Jan 2008 05:01 GMT

> I have 2 tables.
> test1 : A integer, B integer - (1,1) , (2,2)
[quoted text clipped - 4 lines]
>
> Can this be done ?

If A alone is the primary key it is safe to:

update test1 set b = (
select b from test2
where test2.a = test1.a
);

/Lennart

Reply to this Message

Knut Stolze - 10 Jan 2008 09:23 GMT

>> I have 2 tables.
>> test1 : A integer, B integer - (1,1) , (2,2)
[quoted text clipped - 11 lines]
> where test2.a = test1.a
> );

And if you further want to restrict the rows to be updated to those that
have a matching row in table TEST2:

UPDATE test1
SET b = ( SELECT b
FROM test2
WHERE test2.a = test1.a )
WHERE EXISTS ( SELECT b
FROM test2
WHERE test2.a = test1.a )

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Serge Rielau - 10 Jan 2008 11:34 GMT

> I have 2 tables.
> test1 : A integer, B integer - (1,1) , (2,2)
> test2 : A integer, B integer (1,2) , (2,3)
>
> I have to update test1, set test1.b = test2. b where test1.a =
> test2.a

MERGE INTO test1 USING test2 ON test1.a = test2.a
WHEN MATCHED THEN UPDATE SET test1.b = test2.b

It's ANSI/SQL
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Arun Srinivasan - 04 Feb 2008 17:53 GMT

I just love DB2's set operations (dont know if this is the correct
title)...

Reply to this Message