Skip to main content
added 228 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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 ;

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 ;

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 ;
added 244 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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 ;

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) ;

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 ;
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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) ;