CODEWITHSUNDEEP
sql-server
jmClassic
jmClassic

Reputation: 3

Update query error

I am writing the following query to update price in a table(PartsPricing):

Update PartsPricing
set ppPrice = CEILING((select PP.ppPrice from 
                #tmp_PartstoUpdate TPU join PartsPricing PP 
                on TPU.ppPartNumber = PP.ppPartNumber 
                where PP.ppConditionID = 9  and PP.ppDeleted = 0)*.5)
where ppID in (select ppID from #tmp_PartstoUpdate)

When I run the query I get the following error: 

Msg 512, Level 16, State 1, Line 1
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.

I have tried every possible solution I could find online and none of them seemed to work. Any help will be appreciated.

Upvotes: 0

Views: 74

Answers (1)

Ionic
Ionic

Reputation: 3935

Try this instead, this should basically do the same:

Update PP
set ppPrice = CEILING(PP.ppPrice * 0.5)
FROM PartsPricing PP 
INNER JOIN #tmp_PartstoUpdate TPU 
        ON TPU.ppPartNumber = PP.ppPartNumber 
where PP.ppConditionID = 9 and PP.ppDeleted = 0

Your problem is, that your subquery returns more than one row, which causes SQL Server to abort. He can't multiply multiple rows with one value and assign it to another (unless you're using an aggregation).

Upvotes: 1

Related Questions