CODEWITHSUNDEEP
sql-serversql-server-2008
Greg
Greg

Reputation: 15

How to UPDATE a column from two possible database sources

I have the following scenario:

Database A.table A.name
Database A.table A.Application

Database B.table B.name
Database B.table B.Application

Database C.table C.name
Database C.table C.Application

I'm trying to write an UPDATE query that will set a value to table A.Application. The value I need to update it with could come from tables B or C but not both; A.name only exists in either B or C. The condition for each row I would need to update on would be as so:

If B.name exists for A.name, set A.Application = B.application
If C.Name exists for A.name, set A.application = C.application

I'm trying to do this non-dynamically; any assistance would be appreciated.

Upvotes: 0

Views: 100

Answers (2)

maco_wang
maco_wang

Reputation: 21

declare @A table([name] varchar(1),[Application] int)
insert @A
select 'a',0 union all
select 'b',0 union all
select 'c',0
declare @B table([name] varchar(1),[Application] int)
insert @B
select 'a',5 union all
select 'b',6
declare @C table([name] varchar(1),[Application] int)
insert @C
select 'c',8

update @A set [Application]=b.[Application]
from @A a left join
(
select [name],[Application] from @B
union all
select [name],[Application] from @C
) b on a.name=b.name

select * from @A
/*
name Application
---- -----------
a    5
b    6
c    8
*/

Upvotes: 1

bluevector
bluevector

Reputation: 3493

You can do it in two statements:

UPDATE A 
SET A.Application = B.Application
FROM A
INNER JOIN B ON A.name = B.name;

UPDATE A 
SET A.Application = C.Application
FROM A
INNER JOIN C ON A.name = C.name;

Only one of them will actually do anything to the data, assuming the names in B and C are truly orthogonal. Otherwise, C wins.

Or you could get fancy (without having actually tried it):

UPDATE A 
SET A.Application = ISNULL(B.Application, C.Application)
FROM A
LEFT JOIN B ON A.name = B.name
LEFT JOIN C ON A.name = C.name

Upvotes: 1

Related Questions