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?
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() & "')"