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

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 Tabl开发者_StackOverflow社区eA  
   SET TableA.field = (SELECT TableB.field  
                         FROM TableA 
                   INNER JOIN TableB ON TableA.key = TableB.key) 

Any assistance greatly appreciated, as usual!

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)

You said that column returned by the query (TableB.Field) is the primary key. But the issue occurred because of duplicate TableB.Key values. Make sure that TableB.Key is not duplicate for any value. Here you will have to write some login to return only one record in case of subquery returns more than 1 record for a value. For example:

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