Update across 2 tables

**Lennart** · Jan 10 '08, 05:05 AM

Re: Update across 2 tables

On Jan 9, 10:59 pm, Arun Srinivasan <arunro...@gmai l.comwrote:

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 ?

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

**Knut Stolze** · Jan 10 '08, 09:25 AM

Re: Update across 2 tables

Lennart wrote:

On Jan 9, 10:59 pm, Arun Srinivasan <arunro...@gmai l.comwrote:

>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 ?

>
If A alone is the primary key it is safe to:
>
update test1 set b = (
select b from test2
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 )

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

**Serge Rielau** · Jan 10 '08, 11:35 AM

Re: Update across 2 tables

Arun Srinivasan wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Update across 2 tables

Update across 2 tables

Comment

Comment

Comment