1

I have 2 tables

Table A 
address_id email
12341      aba@mail.com
12342      abb@mail.com
12343      abc@mail.com
12344      abd@mail.com
12345      abe@mail.com
12346      abf@mail.com
12347      abg@mail.com
12348      abh@mail.com
12349      abi@mail.com

Table B 
address_id email
12342      xyz@mail.com
12344      xyy@mail.com
12349      xyx@mail.com

I want to update emails in table A with emails in Table B where the address_id matches.

I have tried the below query :

update TableA A 
set email=(select email from TableB B where A.address_id=B.address_id) 

But its updating all the emails to null where the address_id is not matching in TableA.

Appreciate your help..

  • Just add a where to the update. (I am not sure how the exist syntax in DB2 is) – eckes Apr 26 '18 at 14:32
6

An UPDATE without WHERE clause will update all the rows of the table. For those rows that don't have a matching address_id in table B, the subquery returns an empty result set, so the value is updated to NULL.


There's probably a more elegant way to do it but this should only update the matching rows:

update TableA A 
set email = (select email from TableB B where A.address_id = B.address_id)
where exists
      (select 1 from TableB B where A.address_id = B.address_id) ;

Another option is to use MERGE:

MERGE INTO TableA AS A
USING
  ( SELECT address_id, email
    FROM TableB
  ) AS B
  ON A.address_id = B.address_id
WHEN MATCHED THEN
  UPDATE SET email = B.email ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.