April 4, 2017 at 9:48 am
Hi Guys,
I hope all is well...
I am trying to update a pricing field in TableA, by multiplying a field in TableB and joining on an ID and where a qty equals a certain number. The problem is that I believe I have the code, but I am getting the dreaded:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the code:
UPDATE TableA
SET Price = (select round(b.case_price * 1.6,2) from TableA a, TableB b
WHERE a.Id= b.Id and a.Qty = 1 and a.ProductId = 100)
How can I go about doing this without that stinkin' error?
Cheers,
~D
April 4, 2017 at 9:57 am
You're joining TableA to TableB, and getting more than one row from TableB for each row in TableA. You need to decide which one you want to use. How are you going to do that - max value, min value, average value, most recent?
John
April 4, 2017 at 10:07 am
On a separate note I would also suggest using a JOIN clause, rather than a CROSS APPLY and then doing the matching in your WHERE clause:
UPDATE TableA
SET Price = (SELECT ROUND(b.case_price * 1.6,2)
FROM TableA a
JOIN TableB b ON a.Id= b.Id
WHERE a.Qty = 1
AND a.ProductId = 100);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 4, 2017 at 10:35 am
John Mitchell-245523 - Tuesday, April 4, 2017 9:57 AMYou're joining TableA to TableB, and getting more than one row from TableB for each row in TableA. You need to decide which one you want to use. How are you going to do that - max value, min value, average value, most recent?John
I need to update each row, that's why... I'm just not sure how I would go about doing that.
April 4, 2017 at 10:37 am
Thom A - Tuesday, April 4, 2017 10:07 AMOn a separate note I would also suggest using a JOIN clause, rather than a CROSS APPLY and then doing the matching in your WHERE clause:
UPDATE TableA
SET Price = (SELECT ROUND(b.case_price * 1.6,2)
FROM TableA a
JOIN TableB b ON a.Id= b.Id
WHERE a.Qty = 1
AND a.ProductId = 100);
Thank you VERY much!
April 4, 2017 at 11:12 am
Oh my goddness... I figured it out. I had included the update table in my sub query. Rookie mistake! Sheesh!
April 5, 2017 at 1:08 pm
Thom A - Tuesday, April 4, 2017 10:07 AMOn a separate note I would also suggest using a JOIN clause, rather than a CROSS APPLY and then doing the matching in your WHERE clause:
There is no CROSS APPLY here. Perhaps you meant an implicit CROSS JOIN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply