• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Subquery returned more than 1 value Workaround

Hi guys,

I have two tables Products and Cart. Products have QuantityInStock field. Cart has Quantity field. After a customer pays for an order I need to update QuantityInStock in Products table based on the quantities of items in Cart. I have the following statement. It works fine when there is only one type of item in Cart (It can have > 1 quantity). However, if I add a different item I get "Subquery returned more than 1 value," of course. How can I do this differently?

Thank you.

Dim strSQL0 As String = "UPDATE Products SET Products.QuantityInStock = Products.QuantityInStock-" & _
                                    "(SELECT Quantity FROM Cart WHERE Cart.CustomerID='" & _
                                        Membership.GetUser.ProviderUserKey.ToString() & "') WHERE Products.ProductID" & _
                                            "=(SELECT ProductFK1 FROM Cart WHERE Cart.CustomerID='" & _
                                                Membership.GetUser.ProviderUserKey.ToString() & "')"

Open in new window

0
Yury Merezhkov
Asked:
Yury Merezhkov
1 Solution
 
allmerCommented:
Try changing the = to IN

In this case all elements in the subquery will be tested
Dim strSQL0 As String = "UPDATE Products SET Products.QuantityInStock = Products.QuantityInStock-" & _
                                    "(SELECT Quantity FROM Cart WHERE Cart.CustomerID='" & _
                                        Membership.GetUser.ProviderUserKey.ToString() & "') WHERE Products.ProductID" & _
                                            " IN (SELECT ProductFK1 FROM Cart WHERE Cart.CustomerID='" & _
                                                Membership.GetUser.ProviderUserKey.ToString() & "')"

Open in new window

0
 
lwadwellCommented:
Hi RealSnaD,

You are trying to reduce the quantity in stock by the returned value?  ... am I understanding correctly?  If so, try ..

lwadwell
Dim strSQL0 As String = "UPDATE Products SET Products.QuantityInStock = Products.QuantityInStock-" & _
                                    "(SELECT SUM(Quantity) FROM Cart WHERE Cart.CustomerID='" & _
                                        Membership.GetUser.ProviderUserKey.ToString() & "') WHERE Products.ProductID" & _
                                            "=(SELECT ProductFK1 FROM Cart WHERE Cart.CustomerID='" & _
                                                Membership.GetUser.ProviderUserKey.ToString() & "')"

Open in new window

0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
I get the same error after changing it to:
        Dim strSQL0 As String = "UPDATE Products SET Products.QuantityInStock=Products.QuantityInStock-" & _
                                    "(SELECT Quantity FROM Cart WHERE Cart.CustomerID='" & _
                                        Membership.GetUser.ProviderUserKey.ToString() & "') WHERE Products.ProductID IN " & _
                                            "(SELECT ProductFK1 FROM Cart WHERE Cart.CustomerID='" & _
                                                Membership.GetUser.ProviderUserKey.ToString() & "')"

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
lwadwell, thank you but products in cart are different and I cannot use SUM, therefore. Each product in the cart has its own quantity which needs to be deducted from QuantityInStock of the corresponding product in Products table.
0
 
RiteshShahCommented:
what about this query???

update products set product.quantityinstock=product.quantityinstock-cart.quantity
from products join cart
on products.productid=cart.productfk1 and cart.customerid='abc'
0
 
lwadwellCommented:
RealSnaD,

oh, ok ... sounds like you need a predicate in the
    SELECT Quantity FROM Cart WHERE Cart.CustomerID=
to limit it to the product in question.  

Perhaps ...

lwadwell
Dim strSQL0 As String = "UPDATE Products SET Products.QuantityInStock = Products.QuantityInStock-" & _
                                    "(SELECT SUM(Quantity) FROM Cart WHERE Products.ProductID = Cart.ProductFK1 AND Cart.CustomerID='" & _
                                        Membership.GetUser.ProviderUserKey.ToString() & "') WHERE Products.ProductID" & _
                                            "=(SELECT ProductFK1 FROM Cart WHERE Cart.CustomerID='" & _
                                                Membership.GetUser.ProviderUserKey.ToString() & "')"

Open in new window

0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
Worked like a charm! Thank a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now