Creating a SQL lookup

Question

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.

Answer 1

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

Answer 2

Do you need to be concerned about the integrity of the original data at all? Would it be more appropriate to utilize a view and a case statement to generate your numeric fields? Or perhaps more straight forward would be to add a column to your lookup table that contains the numeric fields and then construct a view that only contains that information? There's just something about updating a raw data field that doesn't seem like the optimal solution to me.