Update statement getting error "Subquery returned more than 1 value"
Hello, I need to update a field in a table with data from another table and am getting an error. What's wrong with my statement?
Update #TempMyQuotes
set SOPNumber = (Select b.SOPNumber
from BB_OrderHeader b, #TempMyQuotes a
Where a.QuoteNumber = b.QuoteNumber)
Error Message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Update #TempMyQuotes
set SOPNumber = (Select b.SOPNumber
from BB_OrderHeader b, #TempMyQuotes a
Where a.QuoteNumber = b.QuoteNumber)
Error Message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
ASKER
Thanks for responding Aneeshattingal!
That statement updated all rows with the Max SOPNumber from BB_OrderHeader.
Each row in #TempMyQuotes may or may not have a corresponding row in bb_OrderHeader. The unique key between the two tables is QuoteNumber. If there is a QuoteNumber in #TempMyQuotes and in bb_OrderHeader I need to update #TempMyQuotes .SOPNumber with the bb_OrderHeader.SOPNumber..
The SOPNumber tells the user that the Quote has been ordered.
That statement updated all rows with the Max SOPNumber from BB_OrderHeader.
Each row in #TempMyQuotes may or may not have a corresponding row in bb_OrderHeader. The unique key between the two tables is QuoteNumber. If there is a QuoteNumber in #TempMyQuotes and in bb_OrderHeader I need to update #TempMyQuotes .SOPNumber with the bb_OrderHeader.SOPNumber..
The SOPNumber tells the user that the Quote has been ordered.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I have to do one update at a time via a looping method?? if so how can I accomplish that?
ASKER
Strike that last comment.
This worked after I remove the max
Update a
set SOPNumber = (b.SOPNumber)
from BB_OrderHeader b
INNER JOIN #TempMyQuotes a
ON a.QuoteNumber = b.QuoteNumber
This worked after I remove the max
Update a
set SOPNumber = (b.SOPNumber)
from BB_OrderHeader b
INNER JOIN #TempMyQuotes a
ON a.QuoteNumber = b.QuoteNumber
oops, i forgot to remove that MAX() part :( ....
Update #TempMyQuotes
set SOPNumber = (Select MAX(b.SOPNumber)
from BB_OrderHeader b, #TempMyQuotes a
Where a.QuoteNumber = b.QuoteNumber)