Question 246610 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL procedure to update one booking in bookings table changes all values in bookingslot column

I have no idea why I am having such trouble with this but I can’t get this to work properly. I am trying to create a stored procedure that updates a booking datetime for a bookingid of my choice. I have this:

DELIMITER //

CREATE PROCEDURE UpdateBooking (
IN BookingID INT, 
IN NewSlotTime DATETIME
)
BEGIN 
    DECLARE ConfirmationMessage VARCHAR (255);
    DECLARE UpdatedBookingID INT;
    
   
    SET UpdatedBookingID = BookingID;
    
    UPDATE bookings
    SET BookingSlot = NewSlotTime
    WHERE BookingID = UpdatedBookingID;
    
    
    SET ConfirmationMessage = CONCAT('Booking ID ', UpdatedBookingID, ' has been updated');
    
SELECT ConfirmationMessage as Confirmation;

END; 
//
DELIMITER ;

CALL UpdateBooking (37, '2023-09-14 10:45:00');

I tried this as well:

DELIMITER //

CREATE PROCEDURE UpdateBooking (
    IN BookingID INT, 
    IN NewSlotTime DATETIME
)
BEGIN 
    DECLARE ConfirmationMessage VARCHAR (255);
    
    UPDATE bookings
    SET BookingSlot = NewSlotTime
    WHERE BookingID = BookingID;
    
    SET ConfirmationMessage = CONCAT('Booking ID ', BookingID, ' has been updated');
    
    SELECT ConfirmationMessage as Confirmation;

END; 
//
DELIMITER ;

CALL UpdateBooking (37, '2023-09-14 10:45:00');

Every time I run these codes it comes back the same. There are no errors it just changes all the datetime values in the bookingslot column to what I am calling back for one bookingID. I am using MySQL workbench to run these codes. There are no issues with the database that I am aware of and I have written other stored procedures in this database that worked just fine. This is the first issue I have run into.

I appreciate any help.

Tags:

2

Answers


  1. 0

    Try this:

    DELIMITER //

CREATE PROCEDURE UpdateBooking (
    IN BookingID INT, 
    IN NewSlotTime DATETIME
)
BEGIN 
    DECLARE ConfirmationMessage VARCHAR(255);
    
    UPDATE bookings
    SET BookingSlot = NewSlotTime
    WHERE BookingID = BookingID;  
    
    SET ConfirmationMessage = CONCAT('Booking ID ', BookingID, ' has been updated');
    
    SELECT ConfirmationMessage as Confirmation;

END; 
//
DELIMITER ;
    Login or Signup to reply.
  2. 0

    WHERE BookingID = BookingID – this is always true, and therefore all rows instead of a single one were updated.

    The parser cannot determine that the first BookingID specifies a column name, while the second BookingID is the passed parameter.

    Just use another parameter name, e.g. Booking_ID.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search