cc0 cc0 - 8 days ago 4
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

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;