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
7

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 ;
  • That worked for me, but it still updated all matching rows regardless of whether values had changed or not. I found that by using: WHEN MATCHED AND (A.col1 <> B.col1 or A.col2 <> B.col2) THEN it did the trick. – Tony Payne Dec 19 '19 at 12:03
  • It's never as easy as you would like is it! In this case my target table is a subset (columns) of the source table, and I only want to update it if any of the 12 columns have changed for any row. Updates to the target table are triggered, so I really only want to update where there are actually changes. There are reasons why it's done that way, it would take a book to explain... – Tony Payne Dec 19 '19 at 12:46

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.