在SQL Server中使用JOIN更新表? [英] Update a table using JOIN in SQL Server?

问题描述

我想更新表中的列以在其他表上建立连接，例如:

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'

但是它在抱怨:

第15层，状态1，第2行，消息170
第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更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！