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 ;