samedi 25 janvier 2020

SQL can I create an if statement if subquery returns more than 1 row?

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