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