使用 SQL 批量更新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6629088/
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
Bulk Record Update with SQL
提问by user208662
I have two tables in a SQL Server 2008 environment with the following structure
我在 SQL Server 2008 环境中有两个表，结构如下
Table1
- ID
- DescriptionID
- Description
Table2
- ID
- Description
Table1.DescriptionID maps to Table2.ID. However, I do not need it any more. I would like to do a bulk update to set the Description property of Table1 to the value associated with it in Table2. In other words I want to do something like this:
Table1.DescriptionID 映射到 Table2.ID。但是，我不再需要它了。我想进行批量更新以将 Table1 的 Description 属性设置为 Table2 中与其关联的值。换句话说，我想做这样的事情：
UPDATE
[Table1]
SET
[Description]=(SELECT [Description] FROM [Table2] t2 WHERE t2.[ID]=Table1.DescriptionID)
However, I'm not sure if this is the appropriate approach. Can someone show me how to do this?
但是，我不确定这是否是合适的方法。有人可以告诉我如何做到这一点吗？
回答by Tocco
Your way is correct, and here is another way you can do it:
你的方法是正确的，这是你可以做到的另一种方法：
update Table1
set Description = t2.Description
from Table1 t1
inner join Table2 t2
on t1.DescriptionID = t2.ID
The nested select is the long way of just doing a join.
嵌套选择是仅进行连接的漫长方法。
回答by gbn
Your approach is OK
你的方法没问题
Maybe slightly clearer (to me anyway!)
也许更清楚（无论如何对我来说！）
UPDATE
T1
SET
[Description] = t2.[Description]
FROM
Table1 T1
JOIN
[Table2] t2 ON t2.[ID] = t1.DescriptionID
Both this and your query should run the same performance wise because it is the same query, just laid out differently.
这和您的查询都应该运行相同的性能，因为它是相同的查询，只是布局不同。
回答by George Johnston
You can do this through a regular UPDATEwith a JOIN
你可以通过一个普通UPDATE的JOIN
UPDATE T1
SET Description = T2.Description
FROM Table1 T1
JOIN Table2 T2
ON T2.ID = T1.DescriptionId
回答by Ejaz Waquif
Or you can simply update without using join like this:
或者你可以简单地更新而不使用 join 像这样：
Update t1 set t1.Description = t2.Description from @tbl2 t2,tbl1 t1
where t1.ID= t2.ID
回答by Jesse Webb
The SQL you posted in your question is one way to do it. Most things in SQL have more than one way to do it.
您在问题中发布的 SQL 是一种方法。SQL 中的大多数事情都有不止一种方法可以做到。
UPDATE
[Table1]
SET
[Description]=(SELECT [Description] FROM [Table2] t2 WHERE t2.[ID]=Table1.DescriptionID)
If you are planning on running this on a PROD DB, it is best to create a snapshot or mirror of it first and test it out. Verify the data ends up as you expect for a couple records. And if you are satisfied, run it on the real DB.
如果您计划在 PROD 数据库上运行它，最好先创建它的快照或镜像并对其进行测试。验证数据是否符合您对几条记录的预期。如果您满意，请在真实数据库上运行它。
