SQL query: how do I change a value according to a lookup table?

sql

Solution

I much prefer the following syntax when updating with a join (instead of doing a subquery). It allows you to see results before you update them and know the query's right.

select
   st.key,
   lt.value
--update st set value = lt.value
from
   second_table st
   inner join lookup_table lt on
       st.key = lt.key

Note that when you're ready to update, select everything from `update` on down.

Update: Thanks to tekBlues, I've found out that the above works on SQL Server and Oracle, at the very least. MySQL has a bit different syntax:

update
    second_table st
    inner join lookup_table lt on
        st.key = lt.key
set
    st.value = lt.value

Those are the big RDBMS's, so hopefully one of those is helpful.

Problem

[update: I am using MySQL 4.1.25 ] I think this must be a simple thing to do, but I'm a SQL noob and need some help. I have a lookup table that is something like: lookup_table key1, value1 key2, value2 key3, value3 ... keyN, valueN Then I have another table that has a random list of the keys (with repeats) in one column and I need to add the associated values in the corresponding column. For example, the second table might be: second_table key3, ? key1, ? key1, ? key40, ? I need to replace the ?s in `second_table` with the values from `lookup_table` as follows: second_table (updated) key3, value3 key1, value1 key1, value1 key40, value40 This seems like something that a simple SQL query should address. Any suggestions?

Original source