如何从 SQL Server 中的 SELECT 更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2334712/
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
How do I UPDATE from a SELECT in SQL Server?
提问by jamesmhaley
In SQL Server, it is possible to INSERTinto a table using a SELECTstatement:
在SQL Server 中，可以INSERT使用SELECT语句进入表：
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it also possible to updatevia a SELECT? I have a temporary table containing the values and would like to update another table using those values. Perhaps something like this:
是否也可以通过 a进行更新SELECT？我有一个包含这些值的临时表，并想使用这些值更新另一个表。也许是这样的：
UPDATE Table SET col1, col2
SELECT col1, col2
FROM other_table
WHERE sql = 'cool'
WHERE Table.id = other_table.id
回答by Robin Day
UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE
Table_A.col3 = 'cool'
回答by onedaywhen
In SQL Server 2008 (or better), use MERGE
在 SQL Server 2008（或更好）中，使用 MERGE
MERGE INTO YourTable T
USING other_table S
ON T.id = S.id
AND S.tsql = 'cool'
WHEN MATCHED THEN
UPDATE
SET col1 = S.col1,
col2 = S.col2;
Alternatively:
或者：
MERGE INTO YourTable T
USING (
SELECT id, col1, col2
FROM other_table
WHERE tsql = 'cool'
) S
ON T.id = S.id
WHEN MATCHED THEN
UPDATE
SET col1 = S.col1,
col2 = S.col2;
回答by Jamal
UPDATE YourTable
SET Col1 = OtherTable.Col1,
Col2 = OtherTable.Col2
FROM (
SELECT ID, Col1, Col2
FROM other_table) AS OtherTable
WHERE
OtherTable.ID = YourTable.ID
回答by quillbreaker
I'd modify Robin's excellent answerto the following:
UPDATE Table
SET Table.col1 = other_table.col1,
Table.col2 = other_table.col2
FROM
Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
other_table.col1 IS NOT NULL
AND Table.col1 IS NULL
)
OR (
other_table.col2 IS NOT NULL
AND Table.col2 IS NULL
)
Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.
如果没有 WHERE 子句，您甚至会影响不需要受影响的行，这可能（可能）导致索引重新计算或触发真正不应该被触发的触发器。
回答by SQLMenace
One way
单程
UPDATE t
SET t.col1 = o.col1,
t.col2 = o.col2
FROM
other_table o
JOIN
t ON t.id = o.id
WHERE
o.sql = 'cool'
回答by Martin Smith
Another possibility not mentioned yet is to just chuck the SELECTstatement itself into a CTE and then update the CTE.
另一种尚未提及的可能性是将SELECT语句本身放入 CTE，然后更新 CTE。
;WITH CTE
AS (SELECT T1.Col1,
T2.Col1 AS _Col1,
T1.Col2,
T2.Col2 AS _Col2
FROM T1
JOIN T2
ON T1.id = T2.id
/*Where clause added to exclude rows that are the same in both tables
Handles NULL values correctly*/
WHERE EXISTS(SELECT T1.Col1,
T1.Col2
EXCEPT
SELECT T2.Col1,
T2.Col2))
UPDATE CTE
SET Col1 = _Col1,
Col2 = _Col2
This has the benefit that it is easy to run the SELECTstatement on its own first to sanity check the results, but it does requires you to alias the columns as above if they are named the same in source and target tables.
这样做的好处是很容易SELECT首先自己运行语句来对结果进行完整性检查，但是如果它们在源表和目标表中命名相同，它确实需要您像上面一样为列设置别名。
This also has the same limitation as the proprietary UPDATE ... FROMsyntax shown in four of the other answers. If the source table is on the many side of a one-to-many join then it is undeterministic which of the possible matching joined records will be used in the Update(an issue that MERGEavoids by raising an error if there is an attempt to update the same row more than once).
这也UPDATE ... FROM与其他四个答案中显示的专有语法具有相同的限制。如果源表是一个一对多的多侧面地加入则是undeterministic这可能匹配的联接的记录将在使用Update（一个问题，MERGE通过是否有更新的尝试引发错误避免同一行不止一次）。
回答by Adrian Macneil
For the record (and others searching like I was), you can do it in MySQL like this:
为了记录（和其他人像我一样搜索），你可以在 MySQL 中这样做：
UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id
回答by rageit
Using alias:
使用别名：
UPDATE t
SET t.col1 = o.col1
FROM table1 AS t
INNER JOIN
table2 AS o
ON t.id = o.id
回答by Patrick Frenette
The simple way to do it is:
简单的方法是：
UPDATE
table_to_update,
table_info
SET
table_to_update.col1 = table_info.col1,
table_to_update.col2 = table_info.col2
WHERE
table_to_update.ID = table_info.ID
回答by Ryan
This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).
这可能是执行更新的一个小众原因（例如，主要用于过程中），或者对其他人来说可能是显而易见的，但还应该说明您可以在不使用 join 的情况下执行 update-select 语句（以防万一您正在更新的表没有公共字段）。
update
Table
set
Table.example = a.value
from
TableExample a
where
Table.field = *key value* -- finds the row in Table
AND a.field = *key value* -- finds the row in TableExample a
