0

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 //
ValleTSF
  • 45
  • 1
  • 7
  • `if (EXISTS SELECT * FROM .. WHERE ..)`, and eliminate the count entirely? – user2864740 Jan 25 '20 at 23:49
  • 1
    That said, there are better ways to write an UP[date]-in[SERT] "upsert" (keyword hint) query. This form is also explicitly prone to inconsistent data/results if not wrapped in a serializable transaction. See https://stackoverflow.com/q/4205181/2864740 etc. – user2864740 Jan 25 '20 at 23:49
  • 1
    Anyway, the *actual* issue MySQL is reporting is with the `INSERT` and value when the result is *more than one row*, as the error states. Note that **the two queries (the count and value selection) use different search criteria** and have no strict relationship. Perhaps the goal is to insert *all* order IDs? – user2864740 Jan 25 '20 at 23:55
  • Thanks for the help, maybe there is a way to select the last occurence? – ValleTSF Jan 26 '20 at 08:20

0 Answers0