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!

Solution 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)

Solution 2

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)

Comments

[user761758]

Just wanted to let you know in October of 2015 your answer helped another searching for an elegant way around the 'sub query returned more than one row' conundrum.. Thank you!!

[B5A7]

Not sure if our SQL was at fault but we found that it worked for SQL Server 2016 but not SQL Server 2017. We used Chris Diver's solution here.