在 SQL Server 中使用 JOIN 更新表？

Question

我想更新表中的一列，与其他表进行连接，例如：

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”附近的语法不正确。

这里有什么问题吗？

Answer 1

您还没有完全掌握 SQL Server 专有的 UPDATE FROM 语法。也不知道为什么您需要加入 CommonField 并随后对其进行过滤。试试这个：

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（公用表表达式） -请参阅此处和此处 了解更多详细信息：

;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 是您始终可以在运行时计算的东西，这样做时，永远不必担心结果是陈旧的。

Answer 2

试试这样：

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

Answer 3

Aaron 上面给出的答案是完美的：

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

只是想添加为什么当我们在更新该表时尝试使用表的别名时，在 SQL Server 中会出现此问题，下面提到的语法将始终给出错误：

update tableName t 
set t.name = 'books new' 
where t.id = 1

如果您正在更新一个表，则可以是任何情况单个表或使用连接时更新。

虽然上面的查询在 PL/SQL 中可以正常工作，但在 SQL Server 中却不行。

在 SQL Server 中使用表别名更新表的正确方法是：

update t 
set t.name = 'books new' 
from tableName t 
where t.id = 1

希望它能帮助大家为什么会出现错误。

Answer 4

    UPDATE mytable
         SET myfield = CASE other_field
             WHEN 1 THEN 'value'
             WHEN 2 THEN 'value'
             WHEN 3 THEN 'value'
         END
    From mytable
    Join otherTable on otherTable.id = mytable.id
    Where othertable.somecolumn = '1234'

更多替代方案 这里。

Answer 5

似乎 SQL Server 2012 也可以处理 Teradata 的旧更新语法：

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

如果我没记错的话，当我尝试类似的查询时，2008R2 给出了错误。

Answer 6

MERGE table1 T
   USING table2 S
      ON T.CommonField = S."Common Field"
         AND T.BatchNo = '110'
WHEN MATCHED THEN
   UPDATE
      SET CalculatedColumn = S."Calculated Column";

Answer 7

我发现将 UPDATE 转换为 SELECT 以获得我想要更新的行作为更新之前的测试很有用。如果我可以选择我想要的确切行，我就可以只更新我想要更新的那些行。

DECLARE @expense_report_id AS INT
SET @expense_report_id = 1027

--UPDATE expense_report_detail_distribution
--SET service_bill_id = 9

SELECT *
FROM expense_report_detail_distribution erdd
INNER JOIN expense_report_detail erd
INNER JOIN expense_report er 
    ON er.expense_report_id = erd.expense_report_id 
    ON erdd.expense_report_detail_id = erd.expense_report_detail_id
WHERE er.expense_report_id = @expense_report_id

Answer 8

另一种方法是使用 MERGE

  ;WITH cteTable1(CalculatedColumn, CommonField)
  AS
  (
    select CalculatedColumn, CommonField from Table1 Where BatchNo = '110'
  )
  MERGE cteTable1 AS target
    USING (select "Calculated Column", "Common Field" FROM dbo.Table2) AS source ("Calculated Column", "Common Field")
    ON (target.CommonField = source."Common Field")
    WHEN MATCHED THEN 
        UPDATE SET target.CalculatedColumn = source."Calculated Column";

-Merge 是 SQL 标准的一部分

-而且我很确定内部联接更新是不确定的。
类似的问题在这里，答案谈到了这一点
http://ask.sqlservercentral.com/questions /19089/updating-two-tables-using-single-query.html

Answer 9

我想，这就是您正在寻找的。

UPDATE
    Table1
SET
    Table1.columeName =T1.columeName * T2.columeName
FROM 
    Table1 T1
    INNER JOIN Table2 T2
        ON T1.columeName = T2.columeName;

Answer 10

我遇到了同样的问题..并且您不需要添加物理列..因为现在您必须维护它..
您可以做的是在选择查询中添加通用列：

EX：

select tb1.col1, tb1.col2, tb1.col3 ,
( 
select 'Match' from table2 as tbl2
where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2
)  
from myTable as tbl1

Answer 11

亚伦的上述方法对我来说非常有效。我的更新语句略有不同，因为我需要根据一个表中连接的两个字段进行连接，以匹配另一个表中的字段。

 --update clients table cell field from custom table containing mobile numbers

update clients
set cell = m.Phone
from clients as c
inner join [dbo].[COSStaffMobileNumbers] as m 
on c.Last_Name + c.First_Name = m.Name

Answer 12

使用MYSQL的人

UPDATE table1 INNER JOIN table2 ON table2.id = table1.id SET table1.status = 0 WHERE table1.column = 20

Answer 13

尝试：

UPDATE table1
SET CalculatedColumn = ( SELECT [Calculated Column] 
                         FROM table2 
                         WHERE table1.commonfield = [common field])
WHERE  BatchNO = '110'