SQL update query and 'subquery returned more than one value'

Question

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 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.

Here's the query:

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

Any assistance greatly appreciated, as usual!

Answer 1

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)