题 如何从SQL Server中的SELECT更新?


SQL Server,有可能 insert 使用a进入表 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

3038
2018-02-25 14:36


起源


安迪的评论完全有效。我最近将我的mysql网站转换为PDO,认为我现在在某种程度上可以安全地免受注入攻击。只是在这个过程中我意识到我的一些sql语句仍然是使用用户输入构建的。然后我使用预处理语句修复了它。对于一个完整的新手来说,由于许多专家抛出关于使用PDO的评论而没有指定t,因此并不完全清楚存在差异。 - Zafar Kurbonov


答案:


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'

4463
2018-02-25 14:39



如果您正在编辑表格之间的链接(SET Table.other_table_id = @NewValue)然后将ON语句更改为类似的 ON Table.id = @IdToEdit AND other_table.id = @NewValue - Trisped
这不是错过了 WHERE 问题中的条款?我在这个系统上没有服务器来测试它,但你不能将它添加到 ON 喜欢: ON Table.id = other_table.id AND other_table.sql='cool'?还是我误解了这个问题? - J V
这通过使用UPDATE迭代INNER JOIN来工作。因此,作为WHERE子句的ON函数和INNER JOIN会跳过在JOINed表中找不到的记录。添加WHERE子句也会限制JOINed表的结果集。 @Roger Ray是什么版本的MySQL以及你的查询是什么,因为这个功能如前所述。 - fyrye
@RogerRay,这个问题是关于Microsoft SQL Server的。不幸的是,各种SQL实现之间的语法可能不同。 - Charles Wood
有点相关,我经常喜欢首先将我的UPDATE查询编写为SELECT语句,以便在执行之前可以看到将要更新的数据。塞巴斯蒂安在最近的博客文章中介绍了一种技巧: sqlity.net/en/2867/update-from-select - dennislloydjr


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

672
2017-09-09 09:40



MERGE 也可以用于“Upserting”记录;那是, UPDATE 如果匹配记录存在, INSERT 如果找不到匹配项,则为新记录 - brichins
对于我来说,这比同等更新...加入声明快了大约10倍。 - Paul Suart
MERGE也可用于删除。但要注意MERGE,因为TARGET表不能是远程表。 - Möoz
合并错误: mssqltips.com/sqlservertip/3074/... - Simon D
@SimonD:选择任何SQL Server关键字,你会发现错误。你的观点?我打赌有更多的错误(以及更多基本的错误) UPDATE 比 MERGE人们刚学会与他们一起生活,他们成为景观的一部分(“特色”)。考虑到博客不存在的时候 UPDATE 是一个新的孩子。 - onedaywhen


UPDATE table 
SET Col1 = i.Col1, 
    Col2 = i.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) i
WHERE 
    i.ID = table.ID

524
2018-01-22 17:47



到目前为止最简单!但是你错过了 ID 内部SELECT的字段。你需要这个才能使WHERE子句起作用 - John Doherty
这几乎适用于所有DBMS,这意味着学习一次,到处执行。如果这对您而言比性能更重要,您可能更喜欢这个答案,特别是如果您的更新是一次性更正某些数据。 - Alan Macdonald
@ dotnetN00b stackoverflow.com/a/2334741/206730 对你不好? - Kiquenet


我会修改 罗宾的出色答案 以下内容:

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子句,您甚至会影响不需要受影响的行,这可能(可能)导致索引重新计算或实际上不应该触发的触发器。


253
2017-09-08 21:20



这假设没有列可以为空。 - Martin Smith
你是对的,我正在手工输入这个例子。我已经在where语句中添加了第三个和第四个子句来处理它。 - quillbreaker
WHERE EXISTS(SELECT T1.Col1, T1.Col2 EXCEPT SELECT T2.Col1, T2.Col2)) 更简洁。 - Martin Smith
该语句是否也应该在where子句中包含这两个? (other_table.col1为null且table.col1不为null)或(other_table.col2为null且table.col2不为null) - Barka
取决于是否要使用源中的空值替换目标中的空值。我经常不这样做。但是如果你这样做,马丁建立where子句是最好的选择。 - quillbreaker


单程

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'

180
2018-02-25 14:41





还没有提到的另一种可能性就是扔掉它 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 语句本身首先对sanity检查结果,但是如果在源表和目标表中它们的名称相同,它确实要求您对列进行别名。

这也与专有限制相同 UPDATE ... FROM 语法显示在其他四个答案中。如果源表位于一对多连接的许多一侧,则不确定哪些可能的匹配连接记录将用于 Update (一个问题 MERGE 如果尝试多次更新同一行,则通过引发错误来避免)。


140
2017-11-06 00:18



这个名字有什么意思吗? CTE ? - Raptor
@ShivanRaptor - 它的首字母缩写 公用表表达式。在这种情况下只是一个任意别名。 - Martin Smith
这也适用于多个CTE: ;WITH SomeCompexCTE AS (...), CTEAsAbove AS (SELECT T1.Col1,... FROM T1 JOIN SomeComplexCTE...) UPDATE CTEAsAbove SET Col1=_Col1, ... - VeeTheSecond


对于记录(以及其他像我一样的搜索),你可以在MySQL中这样做:

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

95
2017-10-05 14:20



这很好用。我尝试了以上所有查询,他们都给出了一种或另一种错误。
谢谢!我知道这已经过时了,但我只想说这个适合我。我的服务器不允许在UPDATE语句中使用FROM。所以涉及FROM子句的所有答案都返回了语法错误。这种方式很完美。非常感激。 - Todd Withers
为什么这么经常被投票?如果我发布了记录ruby代码,如果有人要求C#,那就一样了... - isHuman
@isHuman当你寻找my-sql答案时,在sql-server问题中达到这个问题很常见(反之亦然)。这与在c#问题中发布ruby答案完全不同。 - Gabriel


使用别名:

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

78
2018-05-23 13:06





简单的方法是:

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

61
2017-11-14 13:17



你的格式更好;此外,使用子选择时,您的(和Adrian的)工作比其他格式更可靠。谢谢你发布你的答案。 - Ben West
这不是SQl Server语法,它在SQL Server中不起作用 - HLGEM


这可能是执行更新的一个利基理由(例如,主要用于过程),或者对其他人来说可能是显而易见的,但是还应该声明您可以在不使用连接的情况下执行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

48
2018-06-11 16:58





这是另一个有用的语法:

UPDATE suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

它使用“WHERE EXIST”检查它是否为null。


46
2018-05-02 09:48