0

I am trying to create two stored procedures in a MySQL project:

  1. The first one is inserting a new Meal Option, which in MySql is set up to auto-increment the Meal Option Id. How can I get this auto-incremented Meal Option Id and use it in a following insert in the same stored procedure?

    DELIMITER $$ CREATE PROCEDURE SP_InsertMealOption(IN MealName VARCHAR(255), IN MealPrice FLOAT, IN MealType VARCHAR(15), IN ParentSSN VARCHAR(11), IN NutritionistID VARCHAR(11), IN IngredientID INT) BEGIN INSERT INTO Meal_Option (MealName, MealPrice, ParentSSN, NutritionistSSN, IngredientID) VALUES (MealName, MealPrice, ParentSSN, NutritionistSSN, IngredientID); INSERT INTO MealType (MealType, Meal_Option.MealOptionID) VALUES(MealType, MealOptionID); END; $$ DELIMITER ;

  2. I want to make another stored procedure to update two relations. How can I do that?

DELIMITER $$

CREATE PROCEDURE SP_UpdateMealOptionInfoById(IN MealOptionID INT, IN MealName VARCHAR(255), IN MealPrice FLOAT, IN MealType VARCHAR(15))
BEGIN 
UPDATE MealOption AS m, MealType AS mt
SET m.MealName = MealName, m.MealPrice = MealPrice AND mt.MealType = MealType
WHERE m.MealOptionID = MealOptionID AND mt.MealOptionID = MealOptionID;
END;
$$
DELIMITER ;

Thanks!

apache97
  • 35
  • 5
  • 1
    For 1) https://stackoverflow.com/questions/3837990/last-insert-id-mysql 2) I don't understand the question please clarify requirement. – P.Salmon Apr 16 '20 at 07:38
  • The second one is two simultaneous updates for two different relations. Instead of what you see there, I previously had two separate update statements, it would let me create the procedure, but when I ran it I got no feedback. So I changed it to what you see there, which is kind of like a JOIN in an UPDATE statement. Mysql let me do it, but it still won't work. – apache97 Apr 16 '20 at 21:32
  • The set is syntactically correct you separate columns to be updated with commas not and. Otherwise the procedure 'works' as coded. – P.Salmon Apr 17 '20 at 06:45
  • See https://www.db-fiddle.com/f/ivmc97S1higcxQeZuHG6d6/0 – P.Salmon Apr 17 '20 at 06:51

0 Answers0