Solved

Subquery returned more than 1 value Workaround

Posted on 2009-05-11
7
283 Views
Last Modified: 2012-05-06
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
Comment
Question by:RealSnaD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:allmer
ID: 24361656
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 24361714
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24361725
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 7

Author Comment

by:RealSnaD
ID: 24361739
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24361890
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 24361896
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24361928
Worked like a charm! Thank a lot.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question