How to update data from 1 table with subqueries

Question

So, I have 3 columns: building_name, latitude, longitude.

Some of the lats and longs haven't been filled in, however they have in other records. This means I can extract the latitude and longitude on other records matching the building name.

I need to do the following:

1. Get all records with lat and long that are not null and contain a building name
2. Get all records with a null lat or long, but have a building name
3. Update all records (lat and long fields) from 2 with all records from 1 that have a matching building name

What's the best way to go about this?

Answer 1

Try this query. Because I didn't have a sample table, I couldn't test

WITH CTE1 AS 
    (SELECT building_name, latitude, longitude FROM TBL WHERE ISNULL(building_name ,'') <> '' AND ISNULL(latitude ,'') <> '' AND ISNULL(longitude ,'') <> '') , 
    
    CTE2 AS 
    (SELECT building_name, latitude, longitude FROM TBL WHERE ISNULL(building_name ,'') <> '' AND ISNULL(latitude ,'') = '' AND ISNULL(longitude ,'') = '')

    UPDATE CTE2 SET latitude = CTE1.latitude , longitude = CTE1.longitude FROM CTE2 INNER JOIN CTE1 ON CTE2.building_name = CTE1.building_name