updating data

  • 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 ?

  • 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

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

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

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

  • 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 ?

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