Subquery returned more than 1 value. This is not permitted when the subquery follows =, ! =, , >= or when the subquery is used as an expression.

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have written the below query statement to sql server:

UPDATE Product SET Quantity = Quantity + 10 
FROM Product WHERE (SELECT SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = Invoice.ProductId 
INNER JOIN Invoice ON Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY ProductName, MONTH(Date)) > 15

What I have tried:

I know, I have to use WHERE IN, but I don't know how, because I use a different table as the UPDATE.

My task is : increase the quantity in stock by 10 pcs for each product that we sold more than 15 in April.

Posted 29-Apr-20 4:46am

User-14784327

Updated 29-Apr-20 7:25am

v2

Add a Solution

Comments

MadMyche 29-Apr-20 11:18am

Can you please use the Improve Question widget to provide some detail on the table structure as well as what the requirement is?
I have looked at the code without this and the structure I would presume from the naming convention does not align with the code you have presented.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-04-29T06:42:00

Solution 1

You may want to try this out; don't know your exact data structure but this should get you on the right track

SQL

UPDATE Product
SET    Quantity = Quantity + 10 
WHERE  ProductID IN  (
        SELECT ProductID
        FROM       InvoiceLine l
        INNER JOIN Invoice     i  ON l.InvoiceID = i.InvoiceID
        WHERE  Month(Date) = 4 -- is this the actual column name?
        GROUP BY ProductID
        HAVING Sum(InvoiceQuantity) > 15
)

Posted 29-Apr-20 6:42am

MadMyche

Comments

CHill60 29-Apr-20 12:57pm

Interesting (or is it "reassuringly") I came up with the exact same query

To the OP - you should avoid using date as a column name - use something like InvoiceDate instead. If you are going to use it then you should surround it with square brackets - [date]

MadMyche 29-Apr-20 13:39pm

Perhaps Great Minds really do Think Alike

CHill60 · Accepted Answer · 2020-04-29T07:26:00

@MadMyche has shown you the way forward, I am going to dissect your attempt to try to help you see where you went wrong.

When you get an error message like that, start looking at your sub-queries - you only have one and it's in your WHERE clause

SQL

SELECT SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = Invoice.ProductId 
	INNER JOIN Invoice ON Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY ProductName, MONTH(Date)

Firstly - there is a typing error there - you are trying to join tables InvoiceLine and Product based on a column in table Invoice - that is not going to work (or even compile). That should read

SQL

SELECT SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = InvoiceLine.ProductId 
	INNER JOIN Invoice ON Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY ProductName, MONTH(Date)

If you run just that bit of the query you will see that it returns as many rows as you have products. You are trying to test many things against a single value - and that is why you get the error message.

Let's look at that a little deeper - you are GROUPing by month of [date] - but you will only ever have April data because of your WHERE clause - so there is no need to include MONTH([date]) in the GROUP BY

You had already worked out that you needed to use WHERE .. IN but you hadn't identified which Products were IN the criteria that you had set..so try changing the query to

SQL

SELECT Product.ProductID, SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = InvoiceLine.ProductId 
	INNER JOIN Invoice ON #Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY Product.ProductID

Note I've had to tell SQL which table contains the ProductID to group by to avoid the error "Ambiguous column name 'ProductID'." Now when I run that query, I'm still getting one row per product, but now I know which total goes with which product.

To be honest, the only time we care about that total is when it's greater than 15 - so let's put that restriction in now. Note we can't say

SQL

where Month([date]) = 4 AND SUM(InvoiceQuantity) > 15

If you try you will get an error message "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.". That's why @MadMyche's query has

HAVING Sum(InvoiceQuantity) > 15

Now when you run that sub-query you will only get ProductIDs returned that fit your criteria. That collection is the only thing you need and that is what you use for your IN clause - see Solution 1 for the full query.

Looking at your original query it looks as if you were trying to use another construct - where you can update a table using joins. If you wanted to increase the quantity for every product for which there was a sale in April for example you could use

SQL

UPDATE p SET Quantity = Quantity + 10
	from #Invoice i
	inner join #InvoiceLine il on i.InvoiceID = il.InvoiceID
	inner join #Product p on il.ProductID = p.ProductID
	WHERE MONTH(i.[date]) = 4

Note the use of the ALIAS "p". I'm just throwing this example in so you can use it in the future - it won't work for this example because you need to GROUP BY to get total sales.