CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Musikero31
Musikero31

Reputation: 3153

UPDATE table from another table with case statement

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

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?

Upvotes: 1

Views: 14767

Answers (3)

Andomar
Andomar

Reputation: 238078

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

Upvotes: 2

Brian Hooper
Brian Hooper

Reputation: 22044

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;

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

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

Upvotes: 1

Related Questions