Update query
-
hi all check this out, it returns this error ERROR [HYT00] Subquery returns more than 1 row the update query is as follows UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' Why is this and how do I get round it??? Thanks in advance Jetset
-
hi all check this out, it returns this error ERROR [HYT00] Subquery returns more than 1 row the update query is as follows UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' Why is this and how do I get round it??? Thanks in advance Jetset
jetset32 wrote: SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' This is returning more than one row. You need to make sure each cart can only contain each product once, or change your SQL, or do this: UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT TOP 1 ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' The TOP 1 thing is a hack, there is obviously a problem in the data in your database. Christian Graus - Microsoft MVP - C++
-
jetset32 wrote: SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' This is returning more than one row. You need to make sure each cart can only contain each product once, or change your SQL, or do this: UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT TOP 1 ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' The TOP 1 thing is a hack, there is obviously a problem in the data in your database. Christian Graus - Microsoft MVP - C++
-
jetset32 wrote: SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' This is returning more than one row. You need to make sure each cart can only contain each product once, or change your SQL, or do this: UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT TOP 1 ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' The TOP 1 thing is a hack, there is obviously a problem in the data in your database. Christian Graus - Microsoft MVP - C++
-
the TOP 1 thing didnt work, and I have tried every way i know to get this to work! the shopping cart will only contain the productID once, so am still a little clueless on this one! Any one have any ideas? Cheers
jetset32 wrote: the TOP 1 thing didnt work, and I have tried every way i know to get this to work! OK, is there more to the SQL than we're seeing ? ( it ends in a + ). jetset32 wrote: the shopping cart will only contain the productID once, so am still a little clueless on this one! Have you made sure this is the case ? It seems the problem is elsewhere, or the TOP 1 thing would have worked, but that's still the only place you have a subquery that you assume will return one item, and apparently it's not. Have you run the SQL in query analyser, a bit at a time ( like do the select for the product ID first, then if that works, add the rest, etc ) to see what you get ? Christian Graus - Microsoft MVP - C++
-
jetset32 wrote: the TOP 1 thing didnt work, and I have tried every way i know to get this to work! OK, is there more to the SQL than we're seeing ? ( it ends in a + ). jetset32 wrote: the shopping cart will only contain the productID once, so am still a little clueless on this one! Have you made sure this is the case ? It seems the problem is elsewhere, or the TOP 1 thing would have worked, but that's still the only place you have a subquery that you assume will return one item, and apparently it's not. Have you run the SQL in query analyser, a bit at a time ( like do the select for the product ID first, then if that works, add the rest, etc ) to see what you get ? Christian Graus - Microsoft MVP - C++
Hi, The sub query will return more than one product id , this is because the customer has bought more than one item. the query works if only one item is purchased but if more than one item is bought then mysql returns the error more than 1 row is returned, that is correct. I want it to update all the product id's with the quantity that the customer has bought. Can this be done in MySQL, I dont know. Thanks in advance Stuart
-
Hi, The sub query will return more than one product id , this is because the customer has bought more than one item. the query works if only one item is purchased but if more than one item is bought then mysql returns the error more than 1 row is returned, that is correct. I want it to update all the product id's with the quantity that the customer has bought. Can this be done in MySQL, I dont know. Thanks in advance Stuart
OK, then you're going about it completely wrong UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' Why 'WHERE ProductID = ProductID' ? That's the same as WHERE 1=1. UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID = s.ProductID AND s.CartID = '" + Convert.ToString(cartID) + "' Christian Graus - Microsoft MVP - C++
-
OK, then you're going about it completely wrong UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = ProductID and s.CartID='" + Convert.ToString(cartID) + "' Why 'WHERE ProductID = ProductID' ? That's the same as WHERE 1=1. UPDATE Products p, shoppingcart s SET p.Prodquantity = (p.ProdQuantity - s.Quantity) WHERE p.ProductID = s.ProductID AND s.CartID = '" + Convert.ToString(cartID) + "' Christian Graus - Microsoft MVP - C++