在 SQL Server 中使用 JOIN 更新表?

Update a table using JOIN in SQL Server?(在 SQL Server 中使用 JOIN 更新表?)

问题描述

我想更新表中的一列，在其他表上进行连接，例如:

I want to update a column in a table making a join on other table e.g.:

UPDATE table1 a 
INNER JOIN table2 b ON a.commonfield = b.[common field] 
SET a.CalculatedColumn= b.[Calculated Column]
WHERE 
    b.[common field]= a.commonfield
AND a.BatchNO = '110'

但它在抱怨:

消息 170，级别 15，状态 1，第 2 行
第 2 行:'a' 附近的语法不正确.

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.

这里出了什么问题?

推荐答案

您不太了解 SQL Server 专有的 UPDATE FROM 语法.也不确定为什么您需要加入 CommonField 并在之后对其进行过滤.试试这个:

You don't quite have SQL Server's proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this:

UPDATE t1
  SET t1.CalculatedColumn = t2.[Calculated Column]
  FROM dbo.Table1 AS t1
  INNER JOIN dbo.Table2 AS t2
  ON t1.CommonField = t2.[Common Field]
  WHERE t1.BatchNo = '110';

如果你正在做一些非常愚蠢的事情——比如不断尝试将一列的值设置为另一列的聚合(这违反了避免存储冗余数据的原则)，你可以使用 CTE(公用表表达式)- 请参阅此处和此处了解更多详情:

If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:

;WITH t2 AS
(
  SELECT [key], CalculatedColumn = SUM(some_column)
    FROM dbo.table2
    GROUP BY [key]
)
UPDATE t1
  SET t1.CalculatedColumn = t2.CalculatedColumn
  FROM dbo.table1 AS t1
  INNER JOIN t2
  ON t1.[key] = t2.[key];

这真的很愚蠢，原因是每次table2 中的任何行发生更改时，您都必须重新运行整个更新.SUM 是您始终可以在运行时计算的东西，并且在这样做时，永远不必担心结果是陈旧的.

The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in table2 changes. A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.

这篇关于在 SQL Server 中使用 JOIN 更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！