Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Filling holes in query data

Author  Topic 

reidkell
Starting Member

16 Posts

Posted - 2013-09-23 : 17:10:28
Hi, all. This is probably very simple, but is escaping me. Here is a greatly simplified version of a query of mine:

SELECT HostName, IPAddress, SerialNum, Mfg FROM tblMaster

Out of 5K rows, there are 100 with missing serial numbers. I have the missing serial numbers in another table (obtained by another feed), but don't know how to write a single TSQL statement to fill those 100 missing serial numbers. Any help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-23 : 17:11:59
update m
set SerialNum = t.SerialNum
from tblMaster m
join SomeOtherTable t
on m.HostName = t.HostName --or whatever your linking column is
where m.SerialNum is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

reidkell
Starting Member

16 Posts

Posted - 2013-09-25 : 20:41:42
Thank you. What's escaping me is a means of accomplishing this from a SELECT statement, sans an UPDATE. Is this possible?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-26 : 02:27:48
SELECT HostName, IPAddress, SerialNum, Mfg FROM tblMaster
UNION ALL
SELECT HostName, IPAddress, SerialNum, Mfg FROM OtherTable
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-26 : 02:53:58
Using Tara's example.
SELECT		m.HostName, 
m.IPAddress,
COALESCE(m.SerialNum, t.SerialNum) AS SerialNum,
m.Mfg
FROM dbo.tblMaster AS m
LEFT JOIN dbo.SomeOtherTable AS t ON t.HostName = m.HostName --or whatever your linking column is



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

reidkell
Starting Member

16 Posts

Posted - 2013-09-26 : 12:21:41
Yes!! COALESCE did it. I had not used that before. Many thanks!!
Go to Top of Page
   

- Advertisement -