SQL 查询：如何根据查找表更改值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1002036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query: how do I change a value according to a lookup table?
提问by Greg
[update: I am using MySQL 4.1.25 ]
[更新：我使用的是 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:
我认为这一定是一件简单的事情，但我是一个 SQL 菜鸟，需要一些帮助。我有一个类似于以下内容的查找表：
lookup_table
查找表
key1, value1
key2, value2
key3, value3
...
keyN, valueN
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, ?
键 3, ?
键 1, ?
键 1, ?
键 40, ?
I need to replace the ?s in second_tablewith the values from lookup_tableas follows:
我需要second_table用以下值替换 ?s lookup_table：
second_table (updated)
second_table（更新）
key3, value3
key1, value1
key1, value1
key40, value40
key3, value3
key1, value1
key1, value1
key40, value40
This seems like something that a simple SQL query should address. Any suggestions?
这似乎是一个简单的 SQL 查询应该解决的问题。有什么建议？
回答by Eric
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 updateon down.
请注意，当您准备好更新时，请自下而上选择所有内容update。
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:
更新：感谢 tekBlues，我发现上述内容至少适用于 SQL Server 和 Oracle。MySQL 有一些不同的语法：
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.
这些是大型 RDBMS，所以希望其中之一会有所帮助。
回答by Scott Ivey
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
回答by tekBlues
update second_table
set value =
(select value from lookup_Table
where lookup_table.key = second_table.key)
