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

Question

I want to update a column in a table making a join on other table eg:

(我想更新在其他表上进行联接的表中的列，例如：)

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'

But it is complaining :

(但它在抱怨：)

Msg 170, Level 15, State 1, Line 2

(消息170，第15级，州1，第2行)
Line 2: Incorrect syntax near 'a'.

(第2行：“ a”附近的语法不正确。)

What is wrong here?

(怎么了)

  ask by Manjot translate from so

Answer 1

You don't quite have SQL Server's proprietary UPDATE FROM syntax down.

(您不太了解SQL Server专有的UPDATE FROM语法。)

Also not sure why you needed to join on the CommonField and also filter on it afterward.

(同样也不确定为什么您需要加入CommonField并随后对其进行过滤。)

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

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:

(如果您做的事情确实很愚蠢（例如不断尝试将一列的值设置为另一列的集合（这违反了避免存储冗余数据的原则），则可以使用CTE（公用表表达式）-请参见此处并在此处获取更多详细信息：)

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

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.

(这确实很愚蠢的原因是，每当table2任何行发生更改时，您都必须重新运行整个更新。)

A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.

(SUM是您始终可以在运行时计算的内容，因此不必担心结果过时。)