Q:

Creating a SQL lookup

I’m in the process of cleaning up a database table. Due to the way some of the data needed to be processed, now I need to go back and perform a “reverse lookup” on the data. For example, a field for one of the records is set to “car” and I need to set that record’s tranportmode field to “1” (for “car”). The lookup tables are already created. I just need to do the reverse lookup part. The cleansed tables will only have the numeric lookup value. 

A:

UPDATE MyTable
SET MyTable.TransportMode = mlt.TransportMode 
FROM MyTable mt 
INNER JOIN MyLookupTable mlt ON mt.Name = mlt.Name

