November 25, 2009 at 7:43 am
I have an address table (addresses) , one of its columns (county) has some invalid data.
I need to update this column value with data contained in another table, the value will be determined by the address.town contents
The main Town & county data is stored in 2 tables (mastertown & mastercounty) these can be joined by a common mastercountyid column. I need to take the value from address.town, use it as a lookup in the joined mastertown & mastercounty tables and update the address.county column with the value from mastercounty.county
something like
update Address
set address.County =
(select distinct mc.County from MasterCounty mc
INNER JOIN MasterTown mt ON mc.MasterCountyID = mt.MasterCountyID
WHERE mt.TownName = tblAddress.Town)
** this doesnt work as im returning more than one row in the subquery, but its the kind of thing im after
whats the most efficient way to do this ?
November 25, 2009 at 8:02 am
managed to do it myself in the end
update Address set County = mc.County FROM MasterCounty mc
INNER JOIN MasterTown mt ON mc.CountyID = mt.CountyID
WHERE Address.Town = mt.TownName
November 25, 2009 at 1:28 pm
... and it didn't take you long, either. 🙂
Just a couple of concerns for your code and data....
1. Town names aren't unique and any given town name could appear multiple times (maybe even in the same state but not sure on that). The error you got on your first attempt was trying to warn you of that problem. In your second attempt, SQL Server will use whatever county id that comes along "first" and it won't warn you of the mistake you just made. Don't you have a zipcode or something that you could also add to the mix to make sure you're using the correct county?
2. The code you wrote is what I call an "orphaned update". I realize that this is a "one one time use" or "one off" bit of code you've built here but, just so you know, "orphaned updates" can sometimes lose their mind causing a 2 second update to slam multiple CPU's into the wall for a couple of hours.
Here's your code... notice that the target table is the address table and that it doesn't appear in the FROM clause. That's what makes it "orphaned". Sure, the WHERE clause joins it and it all seems to work fine but on certain days when it's a full moon, the river is flowing slower than normal, the walking catfish are move East to West for the night, and you're swing a rubber chicken counter clockwise around your head with your left hand (heh.. ie: Month End crunch time) that type of query will eat your server for dinner.
update Address set County = mc.County FROM MasterCounty mc
INNER JOIN MasterTown mt ON mc.CountyID = mt.CountyID
WHERE Address.Town = mt.TownName
Anytime you have an update with a Join of any type in the FROM clause, you absolutely MUST include the target table in the FROM clause. Like this...
UPDATE dbo.Address
SET County = mc.County
FROM dbo.MasterCountry mc
INNER JOIN dbo.MasterTown mt ON mc.CountyID = mt.CountyID
INNER JOIN dbo.Address ad ON ad.Town = mt.TownName
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2009 at 2:26 am
many thanks for your advice Jeff, much appreciated. I ran your version and it worked in about the same amount of time, but your way has made me much more aware of the pitfalls and is something to remember for any future developments.
November 26, 2009 at 11:30 am
Exactly correct... both methods take the same time... until one day.....
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2009 at 3:08 am
after some initial testing, im still having problems with the actual update, running this query
UPDATE Address
SET County = mc.CountyName
FROM MasterCounty mc
INNER JOIN MasterTown mt ON mc.CountyID = mt.CountyID
INNER JOIN Address ad ON ad.Town = mt.TownName
GO
Ive run it and scanned through the results, they just dont seem correct. In MasterTown there is an entry for Aberdeen, the corresponding county in MasterCounty is Aberdeenshire. Scanning through Address after the update still shows some incorrect entries for Aberdeen, the county is either NULL or 'City Of Aberdeen' (there are even a few entries that say 'Buckinghamshire') so it seems that the update is not working as anticipated. Can you see why this could be ?
November 30, 2009 at 3:20 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 4:39 am
ive worked out what the problem was, I had an additional where clause that checked for null postcodes, that was excluding some rows from the update process. Appologies for not posting the correct SQL, it was an older version that Id copied by mistake.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply