Filling holes in query data - SQL Server Forums

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/

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?

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

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

reidkell
Starting Member

16 Posts

Posted - 2013-09-26 : 12:21:41

Yes!! COALESCE did it. I had not used that before. Many thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources