tags:

views:

87

answers:

2

I have a Microsoft SQL server DB that imports some data which needs a bit of cleanup; some fields need to be remapped based on a second table. For example:

Table: Data
User    Country
Alice   Australia
Bob  Sydney
Carol   London
Dave    London


Table: Translations
From      To
Sydney  Australia
London  United Kingdom

Unfortunately cleaning up the source data is not an option, and this import happens daily so manually changing it is not practical.

What is the easiest way to iterate through the Translationstable, so for each pair an it runs something that is efectively "UPDATE Data SET Country = $TO where Country = $FROM"? If this can be done with a stored procedure that would be ideal. I have a feeling there is a nicely simple way to do this with SQL, but it's beyond my SQL skills and I can't find an answer by searching (probably because it has a really trivial name I don't know :-) )

+2  A: 

Haven't tried it live, but this may work?

UPDATE Data D SET Country = (SELECT To FROM Translations WHERE From = D.Country)

Ariel
It should be correct, but was "To" a reserved word?
chakrit
Unfortunately adds a subquery which on general principles is best avoided given the choice.
le dorfier
comments taken, but hey, for a 10 seconds answer it's not bad ☺
Ariel
This works by left any mapping not covered by the translation table set to NULL (tested on Mysql)
DrStalker
+3  A: 
Update Data
Set data.Country = Translations.[To]
From    Data
        Inner Join Translations
          On data.Country = Translations.[from]
G Mastros
+1 This is the classic form you'll find in the manuals. You can just say "SET Country = ..." and it might be even clearer if you were to alias the second Data (FROM Data AS d ...)
le dorfier
Perfect, and now that I look at it obvious :-)
DrStalker