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

Question

[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?

Answer 1

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.

Answer 2

Along with the other answers, you could also accomplish this with a join...

UPDATE second_table
SET    value = L.value
FROM   second_table S join lookup_table L on S.key = L.key

Answer 3

update second_table 
set value = 
(select value from lookup_Table
where lookup_table.key = second_table.key)