My SQL skills are failing me so I need your help in creating some SQL I can
use in CF or run through Query Analyzer (using SQL Server). I have two
tables as follows:
ZipCodes (900,000 records, CityID column is currently null)
- ZipCode (varchar)
- CityName (varchar)
- Country (varchar)
- Latitude (float)
- Longitude (float)
- CityID (int)
Cities (400 records or so...)
- CityID (int)
- CityName (varchar)
- Country (varchar)
- Latitude (float)
- Longitude (float)
What I need to do is set the CityID column in the ZipCodes table with the
nearest CityID in the Cities table for each ZipCode. The nearest city would
be calculated via Latitude and Longitude coordinates. With the magnitude of
records to update and loop through, I'm having difficulty creating a query
that would do this in a reasonable amount of time.
Any ideas on how best to do this? Can this be done with a SELECT query
within an UPDATE query?
Thanks,
Ben
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
