UPDATE table from another table with case statement

Question

Hi there!

I'm currently having a problem. I need to update Table A from Table B based on this condition:

• If one record in Table A is null (ex. name) then update that record from Table B

Here's my error driven script which I thought from my head. This is what I wanted to happen.

UPDATE TableA
   SET 
      NAME =
      (
         CASE WHEN TableA.NAME IS NULL THEN
            SELECT TableB.NAME
            FROM TableB
            WHERE TableB._ID = 1
      ),
      ADDRESS =
      (
         CASE WHEN TableA.ADDRESS IS NULL THEN
            SELECT TableB.ADDRESS
            FROM TableB
            WHERE TableB._ID = 1
      )
   WHERE TableA._ID = 1

Something like that. Any ideas?

Answer 1

You can join the tables together, and use IsNull to fall back to TableB when TableA is null:

update  a
set     name = IsNull(a.name, b.name)
,       address = IsNull(a.address, b.address)
from    TableA as a
inner join
        TableB as b
on      a._ID = b._ID

Answer 2

Try this -

update a
SET a.name = ( CASE WHEN a.name IS NULL THEN b.name ELSE a.name END ),
    a.address = ( CASE WHEN a.address IS NULL THEN b.address ELSE a.address END )
FROM tableA as a, tableB as b
where a.ID = b.ID

Answer 3

You may find it easier to use two statements...

UPDATE TableA
    SET NAME = (SELECT NAME
                    FROM TableB
                    WHERE TableA.ID = TableB.ID)
    WHERE NAME IS NULL;

UPDATE TableA
    SET ADDRESS = (SELECT ADDRESS
                       FROM TableB
                       WHERE TableA.ID = TableB.ID)
    WHERE ADDRESS IS NULL;