cc0 cc0 - 2 months ago 11
SQL Question

Updating value based on two other values with the use of mapping table

I have two tables; one lookup table which will rarely be updated, and one data table which will be updated frequently.

Lookup table looks like this, where the combination of Code1 and Code2 is unique for each row.

Code1 | Code2 | Classification
AB | CD | Class1
XX | YY | Class2

Data table looks like this;

SomeData | Code1 | Code2 | Classification
foo |AB | CD |
bar |XX | YY |

I need to dynamically update the data table with the Classification corresponding to the unique combination of Code1 and Code2 in the lookup table.

What would be the simplest / most elegant way of achieving this?

Answer Source

You can update 2 table like this

UPDATE table1, table2
SET table2.Classification = table1.Classification
WHERE table1.Code2 = table2.Code2 AND table1.Code1 = table2.Code1;