We help IT Professionals succeed at work.

Updating Data from other tables

Hello-

I have 3 tables:
tblHomes
tblGeo
tblMLS

tblHomes and tblGeo are connected via the listID from tblhomes. TblGeo has the ListID and Lat, long values for each record in tblHomes.

I need to update the Lat and long values based on the lat and long values from tblMLS. TblMLS and tblHomes are connected via the ListNumber but not listID. How can I create a query to do automatic updates of data from one table to another?
This gives me the ListNumber, Latitude and longitude. How do i then insert this into tblGeo and include the ListID from tblHomes.
Select MLS_Listing_Number, Latitude, Longitude from tblMLS where latitude <> '' and longitude <> '' and MLS_Listing_Number in(Select ListingNumber from tblhomes where Active='1')
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

      m.MLS_Listing_Number
      , h.ListID
      , m.Latitude
      , m.Longitude
from tblMLS m
inner join tblHomes h
      on h.MLS_Listing_Number = m.MLS_Listing_Number
where
      m.latitude <> ''
      and m.longitude <> ''
      and m.MLS_Listing_Number in
            (
            Select ListingNumber
            from tblhomes
            where Active='1'
            )

Now that can get turned into an update

Cheers
  David
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Whoops, copy and paste error

select
      m.MLS_Listing_Number
      , h.ListID
      , m.Latitude
      , m.Longitude
from tblMLS m
inner join tblHomes h
      on h.MLS_Listing_Number = m.MLS_Listing_Number
where
      m.latitude <> ''
      and m.longitude <> ''
      and m.MLS_Listing_Number in
            (
            Select ListingNumber
            from tblhomes
            where Active='1'
            )
Senior Database Administrator
CERTIFIED EXPERT
Commented:
Hi,

Update.

update g
set
      g.Latitude = m.Latitude
      , g.Longitude = m.Longitude
from tblGeo g
--select
--      m.MLS_Listing_Number
--      , h.ListID
--      , m.Latitude
--      , m.Longitude
from tblMLS m
inner join tblHomes h
      on h.MLS_Listing_Number = m.MLS_Listing_Number
inner join tblGeo g
      on g.ListID = h.ListID
where
      m.latitude <> ''
      and m.longitude <> ''
      and m.MLS_Listing_Number in
            (
            Select ListingNumber
            from tblhomes
            where Active='1'
            )

Your text asks for an update, but also uses the word insert. Which one?

Cheers
  David

Author

Commented:
David-

It's both actually but the update is fine.

Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.