SQL 更新查询和“子查询返回多个值"

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我使用的是 SQL Server 2008, R2.有一个主表(表 A)，我正在尝试使用临时表(表 B)中的值更新它.

I am using SQL Server 2008, R2. Have a master table (table A), and am trying to update it with values from a temp table (Table B).

SQL Server 出错，说子查询返回了多个值，但是我不明白这是怎么可能的，因为子查询返回的值是表 B 的主键.

SQL Server is erroring out, saying that the subquery returned more than one value, however I don't see how this is possible since the value returned by the subquery is the primary key of Table B.

这是查询:

UPDATE TableA  
   SET TableA.field = (SELECT TableB.field  
                         FROM TableA 
                   INNER JOIN TableB ON TableA.key = TableB.key)

非常感谢任何帮助，像往常一样！

Any assistance greatly appreciated, as usual!

推荐答案

您的子查询根本不相关.子查询中的标识符TableA.key"指的是子查询的 FROM 子句中的 TableA，而不是更新的目标表(恰好也是 TableA).您不想使用两表连接的结果集更新 TableA.field.你只是想要这个:

Your subquery is not correlated at all. The identifier "TableA.key" in the subquery refers to the TableA in the subquery's FROM clause, not the target table of the update (which happens also to be TableA). You don't want to update TableA.field with the result set of a two-table join. You simply want this:

UPDATE TableA  
   SET TableA.field = (SELECT TableB.field  
                       FROM TableB
                       WHERE TableA.key = TableB.key)

相关文章