如何在 SQL Server 中从 SELECT 语句中进行 UPDATE 操作?

4237
SQL Server 中,可以使用 INSERT.. SELECT 语句向表中插入行:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3 
FROM other_table 
WHERE sql = 'cool'

使用SELECT更新表是否也可能?我有一个包含值的临时表,想要使用这些值来更新另一个表。或许可以像这样:

UPDATE Table SET col1, col2
SELECT col1, col2 
FROM other_table 
WHERE sql = 'cool'
WHERE Table.id = other_table.id

1
问题最初的意图是什么?是针对微软的*SQL Server*(T-SQL),还是一般的SQL问题? - Peter Mortensen
2
@PeterMortensen 微软 SQL Server。 - jamesmhaley
39个回答

5981
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'

26
如果你正在编辑表之间的链接(SET Table.other_table_id = @NewValue),那么请将ON语句更改为类似于 ON Table.id = @IdToEdit AND other_table.id = @NewValue 的内容。 - Trisped
2
@CharlesWood 是的。我在MySQL中也有同样的问题。如果有人知道如何在MySQL中实现它并与大家分享,那将是非常好的。我相信很多人都在寻找MySQL版本的解决方案。 - Roger Ray
1
如何在set语句中使用别名?更新表格设置a.col1 = b.col2,从表格a内部连接表格2 b,其中a.id = b.id; 相反,我必须使用update table set table.col1 = b.col2 from table a inner join table2 b on a.id = b.id; - ThinkCode
17
有时候,我喜欢将我的UPDATE查询语句先写成SELECT语句,这样在执行之前就可以看到将会被更新的数据。Sebastian在最近的博客文章中介绍了一种这样做的技巧:http://sqlity.net/en/2867/update-from-select/ - dennislloydjr
3
针对MySQL数据库: UPDATE Table_A, Table_B SET Table_A.col1 = Table_B.col1 WHERE Table_A.id = Table_B.table_a_id - bladekp
显示剩余3条评论

873

在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;

或者:

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;

144
“MERGE”还可以用于“Upserting”记录,即如果存在匹配的记录,则执行“UPDATE”,如果未找到匹配项,则插入新记录。 - brichins
20
这比相应的更新...加入语句快了约10倍。 - Paul Suart
19
MERGE 也可以用于删除操作。但要小心使用 MERGE,因为目标表不能是远程表。 - Möoz
26
合并错误:http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/(注:这是一个关于SQL Server中合并语句需要注意的技巧提示文章) - Simon D
18
@SimonD: 选择任何一个SQL Server关键字,你都会发现存在缺陷。你的观点是什么?我敢打赌与MERGE相比,与UPDATE相关的缺陷更多(而且更基础)。人们已经学会了忍受它们,并将其视为景观的一部分(“特性”)。请考虑到,在UPDATE是新生力量时,博客并不存在。 - onedaywhen
显示剩余9条评论

847
UPDATE YourTable 
SET Col1 = OtherTable.Col1, 
    Col2 = OtherTable.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) AS OtherTable
WHERE 
    OtherTable.ID = YourTable.ID

23
这将适用于几乎所有的数据库管理系统,这意味着只需学习一次,就可以在任何地方执行。如果这对您比性能更重要,特别是如果您的更新只是为了纠正一些数据,那么您可能更喜欢这个答案。 - Alan Macdonald
3
如果您需要从第二个表中设置第一个表的聚合数据,可以将聚合数据放在选择子查询中,因为不能执行 SET Table_A.col1 = SUM(Table_B.col1)(或任何其他聚合函数)。因此,在此情况下,这种方法比 Robin Day 的答案更好。 - Jason S
我真的很喜欢这个解决方案,因为它感觉就像是对 INSERT ... SELECT 工作方式的自然补充。谢谢分享! - Anthony Iacono
谢谢。这是最简单和最易懂的方式。 - Shehan Silva

307

我会修改Robin的出色回答,使其变为以下形式:

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
)

没有 WHERE 子句,你会影响到不需要被影响的行,这可能会导致索引重新计算或触发本不应该触发的触发器。


7
这假设所有的列都不可为空。 - Martin Smith
4
没错,我是手打这个例子的。我已经添加了第三个和第四个条件子句到where语句中来处理这个问题。 - quillbreaker
49
WHERE EXISTS(SELECT T1.Col1, T1.Col2 EXCEPT SELECT T2.Col1, T2.Col2)) 这个语句更加简洁。 - Martin Smith
马丁 - 我花了一些时间才掌握那个语句的技巧。"它是一个where子句中的select,但它不是一个表子查询?" 这是一个我很难理解的命题。现在我已经明白了,我发现这是一种非常有价值的技术,特别是对于某些数据仓库操作。 - quillbreaker
5
这个语句不应该也包含这两个条件吗? (other_table.col1 为空且 table.col1 不为空)或者 (other_table.col2 为空且 table.col2 不为空) - Barka
4
取决于您是否希望将目标中的空值替换为源中的空值。通常情况下,我不会这样做。但如果您想这么做,Martin构造的where子句是最好的选择。 - quillbreaker

231

一种方式

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'

SQL Server 12。看起来它是从JOIN t获取t.id,而不是从UPDATE t获取。因此,无论我们尝试更新t的哪一行,它始终使用相同的值进行更新。 - Pouria Moosavi

192

还有一种可能性尚未被提及,那就是将 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;

这种方法的好处在于可以先单独运行SELECT语句以检查结果,但如果源表和目标表中有同名列,则需要像上面那样使用别名。

这种方法与四个其他答案中显示的专有的UPDATE ... FROM语法具有相同的限制。如果源表在一对多连接的“多”侧,则无法确定将使用哪个可能匹配的连接记录进行更新(MERGE通过在尝试更新同一行超过一次时引发错误来避免此问题)。


4
CTE 这个名字有什么含义吗? - Raptor
21
@ShivanRaptor - 这是 公用表达式 的缩写。 在这种情况下,只是一个任意的别名。 - Martin Smith
3
在使用多个CTE时,此语法也可以正常工作: ;WITH SomeCompexCTE AS (...), CTEAsAbove AS (SELECT T1.Col1,... FROM T1 JOIN SomeComplexCTE...) UPDATE CTEAsAbove SET Col1=_Col1, ... - VeeTheSecond

141

提供记录(也为了其他像我一样在搜索的人),你可以在MySQL中这样做:

UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id

112

使用别名:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

83

简单的方法是:

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

25
这不是 SQL Server 语法,在 SQL Server 中无法使用。 - HLGEM

74

这可能是执行更新的一个小众原因(例如,在过程中主要使用),或者对其他人来说很明显,但也应该指出,您可以在不使用 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

1
我认为这应该是被接受的答案,因为它保持简单明了。 - Panzercrisis

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,