asked on

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')

David Todd

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 Todd

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'
            )

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfergy

ASKER

David-

It's both actually but the update is fine.

Thanks