updating data

Question

Post reply

updating data

solus

Hall of Fame

Points: 3495
More actions
November 25, 2009 at 7:43 am

#385849

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 ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

solus Hall of Fame Points: 3495 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

... 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

solus Hall of Fame Points: 3495 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Exactly correct... both methods take the same time... until one day.....

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

solus Hall of Fame Points: 3495 More actions · Answer 5

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

solus Hall of Fame Points: 3495 More actions · Answer 7

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.