September 12, 2011 at 12:44 pm
I have an address table with following columns:
1. address_id int
2. street_address varchar
3. city varchar
4. state char
5. zip_code char
6. latitude decimal
7. longitude decimal
The last two columns, latitude and longitude, are empty now.
I found another table, with similar data structures, and had latitude/longitude data populated already. I'm trying to compare two tables, if found same addresses, then update my table's latitude/longitude columns with the data from the second table. Let's say, if street_address and zip_code fields are matched, I'll assume they are the same address, then update my table's latitude/longitude columns with data from the second table.
Please help.
September 12, 2011 at 12:57 pm
Something like this.
Update MyTable
set MyField = b.Value
from MyTable mt
join OtherTable b on mt.KeyField = b.KeyField
--optional where clause or whatever filtering here
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2011 at 7:32 am
This is pretty much what Sean has posted. More customized.
--a1 is the table that's missing lat-long info
update a1
set a1.latitude = a2.latitude, a1.longitude = a2.longitude
from address1 a1
join address2
on a2.street_address = a1.street_address
and a2.zip_code = a1.zip_code
Read up Merge -
http://www.techrepublic.com/blog/datacenter/using-sql-server-2008s-merge-statement/194
http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/[/url]
Delete using join -
http://connectsql.blogspot.com/2011/02/sql-server-how-to-delete-or-update-rows.html
Types of joins -
http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx
Apply -
http://www.sqlservercentral.com/articles/APPLY/69953/[/url]
That should help you deal with common scenarios.
https://sqlroadie.com/
September 16, 2011 at 7:28 am
Thanks, I was able to upadte my table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply