I have a table with the following data:
CUSIP SEDOL DESC
1111 NULL ABC Corp
1234 NULL ABCD Corp
NULL 12 ABCDE Corp
Now I have another table on a different databse server:
CUSIP SEDOL DESC
1111 18 ABC Corp
1234 19 ABCD Corp
1246 12 ABCDE Corp
How do i go about in populating the NULL values in the first table based on the values provided in table in a different database server? (I'm using SQL Server 2005)
update table1 set
sedol = (select sedol from database2.table2 where desc = table1.desc)
where sedol is null;
It is not clear from your question if cusip is what's important, so you might need instead:
update table1 set
sedol = (select sedol from database2.table2 where cusip = table1.cusip)
where sedol is null;
To update cusip, use this:
update table1 set
cusip = (select cusip from database2.table2
where desc = table1.desc
and sedol = table2.sedol)
where cusip is null;
coalesce() and adding where sedol is null is semantically equivalent, but more efficient, as only those rows needing update are locked for update, and potentially much more efficient due to avoiding locks and avoiding many pointless subqueries.Update BadTable set BadTable.SEDOL=Coalesce(BadTable.SEDOL,GoodTable.SEDOL) from GoodTable where GoodTable.[DESC]=BadTable.[DESC]
maybe
where GoodTable.[CUSID]=BadTable.[CUSID]
update table1 t1
set t1.sedol
= coalesce (t1.sedol,(select top 1 sedol
from table2
where t1.cusip = cusip
and t1.desc = desc))
First add a linked server as per Tim's comment.
Then a number of queries like this depdning on matching rules.
Update
table1
Set
Sedol = t2.Sedol
From
table1 t1
Inner Join
server2.db2.schema2.table2 t2
On t1.CusIP = t2.CusIP and t1.[Desc] = t2.[Desc] And t1.Sedol Is Null
After add linked server you script will be look
MERGE dbo.your_table AS target
USING [AnotherServer].dbo.your_table AS source
ON (target.[DESC] = source.[DESC])
WHEN MATCHED AND (target.CUSIP IS NULL OR target.SEDOL IS NULL)
THEN UPDATE SET target.CUSIP = source.CUSIP,
target.SEDOL = source.SEDOL;
