Replacing NAs in column with values from another table SQL Server

Question

This is my first time posting to stack overflow and I know that similar questions have been asked before, so I apologize in advance. However, I can't seem to work around this regardless of solutions given in other forums.

I have two tables in SQL Server, one contains storm event info and the other contains county info. They both contain identical county_fips numbers. Neither of these contain primary or foreign keys.

I need to replace the NAs from the storm event info with the lat/lons from the county table. Is this possible without foreign/primary key relationships?

The StormEvent table looks like this:

ID | Lat | Lon | State_FIPS | County_FIPS 
------------------------------------------
1  |  33 | -88 |    028     |    087
2  |  31 | -98 |    048     |    225
3  |  NA |  NA |    017     |    034
4  | 39  | -100|    020     |    063

and so on...

The CountyTable looks like this (statefp10, countyfp10 = FIPS; intptlat10, intptlon10 = lat/lon):

  StateFP10 | County_FP10   | State_FP10| intptlat10 | intptlon10
   --------------------------------------------------------------
    1       |  087          | 028       |    33      |  -88
    2       |  225          | 048       |    31      |  -98     
    3       |  034          | 017       |    45      |  -102

And so on.

So far I've tried the following code to adjust the Lat column with slight variations:

UPDATE n 
SET n.lat= c.intptlat10
FROM StormEvent n 
INNER JOIN CountyTable c ON n.County_FIPS= c.CountyFP10
WHERE n.LAT = 'NA' 

The query is executed, and I'm told x amount of rows were affected, but when I write a select statement to retrieve all of the NAs included in the Lats for the StormEvent table, they are still there.

If anyone can point me in the right direction it would be greatly appreciated! Again, I apologize if I am going about this the wrong way, this is my first post and I am a SQL novice.

Answer 1

Assuming you don't actually need to set the data, but you just want to see lat/lon data when you query, you should be able to do something like this:

select 
a.ID, 
case a.Lat when 'NA' then b.intptlat10 else a.Lat end as Lat, 
case a.Lon when 'NA' then b.intptlon10 else a.Lon end as Lon, 
a.State_FIPS, 
a.County_FIPS 
from StormEvent a
left join CountyTable b
on a.State_FIPS = b.StateFIPS;

Answer 2

You may just need to update your JOIN. I've created some table variables to test with.

DECLARE @StormEvent TABLE (ID INT, Lat VARCHAR(10), Lon VARCHAR(10), 
                            State_FIPS INT, County_FIPS INT)
INSERT @StormEvent (ID , Lat, Lon, State_FIPS, County_FIPS)
VALUES  (1,'33','-88',028,087),
        (2,'31','-98',048,225),
        (3,'NA','NA',017,034),
        (4,'39','-100',020,063)


DECLARE @CountyTable TABLE (StateFP10 INT, County_FP10   INT, State_FP10 INT, 
                            intptlat10 VARCHAR(10), intptlon10 VARCHAR(10))
INSERT @CountyTable
VALUES  (1,087,028,'33','-88'),
        (2,225,048,'31','-98'),
        (3,034,017,'45','-102')

UPDATE n 
SET n.lat= c.intptlat10, n.Lon = c.intptlon10
FROM @StormEvent n 
INNER JOIN @CountyTable c ON n.State_FIPS= c.State_FP10
WHERE n.LAT = 'NA'

SELECT *
FROM @StormEvent

Assuming you want both N/A values updated, just add the Lon column to your UPDATE.

SET n.Lat = c.intptlat10, n.Lon = c.intptlon10

Results:

ID  Lat Lon     State_FIPS   County_FIPS
1   33  -88     28           87
2   31  -98     48           225
3   45  -102    17           34
4   39  -100    20           63