Hi I'm making a procedure "AddToCart" I want to check if there is row with the same column values and if there is update it, if not insert a new row. I keep getting subquery returns more than 1 row.
CREATE PROCEDURE AddToCart(thisCustomerID INT, thisOrdersID INT, thisShoeID INT, thisQuantity INT)
BEGIN
DECLARE any_rows_found int;
IF (thisOrdersID IS NULL)
THEN
INSERT INTO orders (Date, CustomerID)
VALUES (CURRENT_DATE, thisCustomerID);
SELECT COUNT(*)
INTO any_rows_found
FROM Shoe_Orders
WHERE OrdersID = thisOrdersID
AND ShoeID = thisShoeID;
IF (any_rows_found > 0)
THEN
UPDATE shoe_orders
SET Quantity = Quantity + thisQuantity
WHERE ShoeID = thisShoeID
AND OrdersID = thisOrdersID;
ELSE
INSERT INTO shoe_orders (ShoeID, OrdersID, Quantity)
VALUES (thisShoeID, (SELECT ID
FROM orders
WHERE CustomerID = thisCustomerID
AND Date = CURRENT_DATE), thisQuantity);
END IF;
END IF;
END //
Aucun commentaire:
Enregistrer un commentaire